Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
I have a pivot table where I need a specific result of a dimension to be kept out if the date selected is 2023 or older, but it needs to be part of the pivot table if 2024+ is selected.
Example: If the user selects Year/Year-Qtr that is less than or equal to quarter 2023 then the row Dog would not be part of the pivot table, total, etc. Only Cat and Rat data would be.
Quarter | 2023-3 | 2023-3 | 2023-3 | 2023-4 | 2023-4 | 2023-4 | |
Type | Name | Actual | Plan | % of Plan | Actual | Plan | % of Plan |
Animal | Cat | $500 | $1,000 | 50% | $0 | $1,500 | 0% |
Animal | Rat | $1,000 | $2,000 | 50% | $0 | $3,250 | 0% |
Animal | Total | $1,500 | $3,000 | 50% | $0 | $4,750 | 0% |
However, if the user selects Year/Year-Qtr that is greater than or equal to quarter 2024 then Dog would be part of the pivot table, total, etc along with Cat and Rat.
Quarter | 2024-1 | 2024-1 | 2024-1 | 2024-2 | 2024-2 | 2024-2 | |
Type | Name | Actual | Plan | % of Plan | Actual | Plan | % of Plan |
Animal | Cat | $500 | $1,000 | 50% | $0 | $1,500 | 0% |
Animal | Rat | $1,000 | $2,000 | 50% | $0 | $3,250 | 0% |
Animal | Dog | $250 | $500 | 50% | $0 | $1,000 | 0% |
Animal | Total | $1,750 | $3,500 | 50% | $0 | $5,750 | 0% |
Is this where Set Analysis would be used? Please let me know if you need more detail. I am trying to add the equation as a Calculated Dimension under the dimension called "Name".
Right now the calculation in there keeps Dog out no matter what year/year-qtr is selected.
if(Name<> 'Dog', [Name])
Again, I just need "Dog" kept out if year/year-qtr is 2023 or older.
Thanks!
Try this Calculated Dimension.
=If(WildMatch(GetFieldSelections(Quarter),'*2013*'),
If(Name<>'Dog',Name),Name)
Try this Calculated Dimension.
=If(WildMatch(GetFieldSelections(Quarter),'*2013*'),
If(Name<>'Dog',Name),Name)
Hi kaushiknsolanki,
Thanks for the quick response! I think that may have done the trick! I will play around with it now, but so far it is looking accurate!
Good to hear that. If that works please mark the post with solution as "Accept As Solution", so that other people on community can see that and don't forget to like the comment. 🙂