This is a collection of tidbits that Shawn Wallwork discovered while answering (or reading) questions on the Tableau Forums.
This is a collection of tidbits that Shawn Wallwork discovered while answering (or reading) questions on the Tableau Forums.
H, Shawn:
I found you on Tableau forums and wanted some help from you for a problem that I have been trying to solve in Tableau. I would greatly appreciate it if you could help me here. Thanks in advance!
I have the following dataset:
Quarter date loss id reserve
Q1 Jan-15 8 $5,319
Q1 Jan-15 6 $3,936
Q1 Jan-15 3 $1,664
Q1 Jan-15 2 $9,959
Q1 Feb-15 10 $6,961
Q1 Feb-15 7 $8,114
Q1 Feb-15 6 $854
Q1 Feb-15 4 $4,481
Q1 Mar-15 9 $8,452
Q1 Mar-15 7 $4,019
Q1 Mar-15 6 $5,372
Q2 Apr-15 2 $2,005
Q2 Apr-15 2 $539
Q2 Apr-15 8 $7,057
Q2 Apr-15 8 $1,741
Q2 May-15 8 $1,452
Q2 May-15 8 $5,318
Q2 May-15 9 $1,576
Q2 May-15 10 $9,787
Q2 Jun-15 2 $6,201
Q2 Jun-15 7 $4,445
Q2 Jun-15 10 $8,697
Q2 Jun-15 10 $4,296
Now, if I filter for only Q1, I want the “lossid” and “reserve” amount corresponding to the max value of date. For example if I filter for Q1 then then following records remain and I have mentioned which should be retained and which should be ignored:
Quarter date lossid reserve
Q1 Feb-15 10 $6,961 – retain
Q1 Mar-15 9 $8,452 – retain
Q1 Jan-15 8 $5,319 – retain
Q1 Mar-15 7 $4,019 – retain
Q1 Feb-15 7 $8,114 – ignore
Q1 Mar-15 6 $5,372 – retain
Q1 Feb-15 6 $854 – ignore
Q1 Jan-15 6 $3,936 – ignore
Q1 Feb-15 4 $4,481 – retain
Q1 Jan-15 3 $1,664 – retain
Q1 Jan-15 2 $9,959 – retain
that is, the final view should be like this:
Quarter date lossid reserve
Q1 Feb-15 10 $6,961
Q1 Mar-15 9 $8,452
Q1 Jan-15 8 $5,319
Q1 Mar-15 7 $4,019
Q1 Mar-15 6 $5,372
Q1 Feb-15 4 $4,481
Q1 Jan-15 3 $1,664
Q1 Jan-15 2 $9,959
I have more than 10 quarters of data and the filter can be applied any quarter(s) or year or month and the view should dynamically change as per the rules I have mentioned above.
Further more I would like to calculate the aggregate reserve amount for that period (filter). Continuing with the above example:
Period – Total_reserve
Q1 – $48,886
LikeLike
Aniket this really is best to post something like this on the forums for a couple of reasons: 1 – You may get more than one answer, and you can chose the one that works best for you, 2 – You can attach a sample workbook so we can more easily understand and answer your question, 3 – Other folks will learn from reading the discussion.
So please go over to the forums, post your question with a sample packaged workbook, and then if you want come back here and post a link to your question. If I get a link, I’ll post a reply.
Cheers,
–Shawn
LikeLike
I have already done that Shawn. Here is the link to the discussion thread that I started.
http://community.tableau.com/message/439558#439558?sr=tcontent
I apologize for the inconvenience.
LikeLike