topic Set Analysis Needed? Keeping One Specific Object Result out of Table based on Year/Year-Qtr Selected in App Development

Set Analysis Needed? Keeping One Specific Object Result out of Table based on Year/Year-Qtr Selected

trazerr1 — Mon, 11 Dec 2023 18:48:21 GMT

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!

Re: Set Analysis Needed? Keeping One Specific Object Result out of Table based on Year/Year-Qtr Selected

kaushiknsolanki — Mon, 11 Dec 2023 18:58:08 GMT

Try this Calculated Dimension.

=If(WildMatch(GetFieldSelections(Quarter),'*2013*'),
If(Name<>'Dog',Name),Name)

Re: Set Analysis Needed? Keeping One Specific Object Result out of Table based on Year/Year-Qtr Selected

trazerr1 — Mon, 11 Dec 2023 19:07:24 GMT

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!

Re: Set Analysis Needed? Keeping One Specific Object Result out of Table based on Year/Year-Qtr Selected

kaushiknsolanki — Mon, 11 Dec 2023 19:09:38 GMT

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. 🙂