Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
if(sum(if(SQF_Committed_YearQuarter='Q'&ceil(Month(Today())/3)&'_'& year(Today()),SQF_Committed)) <=0 ,
(sum(if(SQF_Committed_YearQuarter='Q'&ceil(Month(Today())/3)&'_'& year(Today()),SQF_Committed_Planned))),
(sum(if(SQF_Committed_YearQuarter='Q'&ceil(Month(Today())/3)&'_'& year(Today()),SQF_Committed))))
How can I put the above statement in set analysis ?
I get diff values when I do Sum of rows and Expression total
This?
=Sum(Aggr(IF(sum(if(SQF_Committed_YearQuarter='Q'&ceil(Month(Today())/3)&'_'& year(Today()),SQF_Committed)) <=0,
sum(if(SQF_CommittedPlanned_YearQuarter='Q'&ceil(Month(Today())/3)&'_'& year(Today()),SQF_Committed_Planned)),
sum(if(SQF_Committed_YearQuarter='Q'&ceil(Month(Today())/3)&'_'& year(Today()),SQF_Committed))), [Lease Type], LeaseCodeKey))
Create flag in the script
If(SQF_Committed_YearQuarter >= QuarterStart(Today()) and SQF_Committed_YearQuarter <= QuarterEnd(Today()), 1, 0) as Flag
and then this:
If(Sum({<Flag = {1}>} SQF_Committed) <= 0, Sum({<Flag = {1}>} SQF_Committed_Planned), Sum({<Flag = {1}>} SQF_Committed))
To address Sum of rows issue, you can use Sum(Aggr(...))
Sum(Aggr(If(Sum({<Flag = {1}>} SQF_Committed) <= 0, Sum({<Flag = {1}>} SQF_Committed_Planned), Sum({<Flag = {1}>} SQF_Committed)), <DimensionsHere>))
Thank you Sunny.
PFA files.I cannot get to match Expression total to Sum of rows.
This?
=Sum(Aggr(IF(sum(if(SQF_Committed_YearQuarter='Q'&ceil(Month(Today())/3)&'_'& year(Today()),SQF_Committed)) <=0,
sum(if(SQF_CommittedPlanned_YearQuarter='Q'&ceil(Month(Today())/3)&'_'& year(Today()),SQF_Committed_Planned)),
sum(if(SQF_Committed_YearQuarter='Q'&ceil(Month(Today())/3)&'_'& year(Today()),SQF_Committed))), [Lease Type], LeaseCodeKey))
Awesome. Thank YOU.
You are the best