Set Analysis

[apthansh]

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

[sunny_talwar]

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))

[sunny_talwar]

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>))

[apthansh]

Thank you Sunny.

PFA files.I cannot get to match Expression total to Sum of rows.

[sunny_talwar · Accepted solution]

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))

[apthansh]

Awesome. Thank  YOU.

You are the best