topic Re: Set Analysis in QlikView

Set Analysis

apthansh — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Set Analysis

sunny_talwar — Mon, 05 Jun 2017 17:02:00 GMT

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

Re: Set Analysis

sunny_talwar — Mon, 05 Jun 2017 17:02:59 GMT

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

Re: Set Analysis

apthansh — Mon, 05 Jun 2017 18:21:55 GMT

Thank you Sunny.

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

Re: Set Analysis

sunny_talwar — Mon, 05 Jun 2017 18:33:40 GMT

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

Re: Set Analysis

apthansh — Mon, 05 Jun 2017 19:30:15 GMT

Awesome. Thank YOU.

You are the best