Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have a dimension with the following expression:
if(AGGR(
RangeMin(12 / ($(MedFH) /ACCOMP_FH),12 / ($(MEDFC) / ACCOMP_FC),ACCOMP_MO),TASK_COUNT) < 288,'In Range','Out of Range')
I have this is a filter box so I can filter to only show "in range" data.
I am starting to have issues with it. When I make a selection and then go back I will need to choose the filter over and over again.
I'm thinking that I need to use the expression in set analysis to limit the other measures one by one.
I have set up a variable.
InRange = AGGR(
RangeMin(12 / ($(MedFH) /ACCOMP_FH),12 / ($(MEDFC) / ACCOMP_FC),ACCOMP_MO),TASK_COUNT
Here is an existing measure that I would like to limit:
((sum(aggr(sum(Total <TASKNUMBER> TASKMH),TASK_COUNT, TASKNUMBER))+
sum(aggr(sum(Total <TASKNUMBER> ACCESSMH),TASK_COUNT, TASKNUMBER))+
sum(aggr(sum(Total <TASKNUMBER> PREPMH),TASK_COUNT, TASKNUMBER)))
* 75 * $(Efficiency Factor))/24
How (and where) can I use this in set analysis in this case? Here is my start
((sum(aggr(sum({<$(InRange)>}Total <TASKNUMBER> TASKMH),TASK_COUNT, TASKNUMBER))+
sum(aggr(sum({<$(InRange)>}Total <TASKNUMBER> ACCESSMH),TASK_COUNT, TASKNUMBER))+
sum(aggr(sum({<$(InRange)>}Total <TASKNUMBER> PREPMH),TASK_COUNT, TASKNUMBER)))
* 75 * $(Efficiency Factor))/24
It might be easy to help if you can share a sample or some sample data. and from that what output do you expect to see?
Dont worry. I cracked it.
((sum(aggr(sum({<TASK_COUNT = {"=AGGR(RangeMin(12 / ($(MedFH) /ACCOMP_FH),12 / ($(MEDFC) / ACCOMP_FC),ACCOMP_MO),TASK_COUNT) < 288"}>} Total <TASKNUMBER> TASKMH),TASK_COUNT, TASKNUMBER))
+
sum(aggr(sum({<TASK_COUNT = {"=AGGR(RangeMin(12 / ($(MedFH) /ACCOMP_FH),12 / ($(MEDFC) / ACCOMP_FC),ACCOMP_MO),TASK_COUNT) < 288"}>}Total <TASKNUMBER> ACCESSMH),TASK_COUNT, TASKNUMBER))
+
sum(aggr(sum({<TASK_COUNT = {"=AGGR(RangeMin(12 / ($(MedFH) /ACCOMP_FH),12 / ($(MEDFC) / ACCOMP_FC),ACCOMP_MO),TASK_COUNT) < 288"}>}Total <TASKNUMBER> PREPMH),TASK_COUNT, TASKNUMBER)))
* 75 * $(Efficiency Factor))/24
I won't worry... 🙂
Anyways, you can further simplify your expression to this (no need for Aggr() in your set analysis)
((Sum(Aggr(Sum({<TASK_COUNT = {"=RangeMin(12 / ($(MedFH) /ACCOMP_FH),12 / ($(MEDFC) / ACCOMP_FC), ACCOMP_MO) < 288"}>} TOTAL <TASKNUMBER> TASKMH), TASK_COUNT, TASKNUMBER)) + Sum(Aggr(Sum({<TASK_COUNT = {"=RangeMin(12 / ($(MedFH) /ACCOMP_FH),12 / ($(MEDFC) / ACCOMP_FC), ACCOMP_MO) < 288"}>}TOTAL <TASKNUMBER> ACCESSMH), TASK_COUNT, TASKNUMBER)) + Sum(Aggr(Sum({<TASK_COUNT = {"=RangeMin(12 / ($(MedFH) /ACCOMP_FH),12 / ($(MEDFC) / ACCOMP_FC), ACCOMP_MO) < 288"}>}TOTAL <TASKNUMBER> PREPMH),TASK_COUNT, TASKNUMBER))) * 75 * $(Efficiency Factor))/24