Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Become an analytics expert with Qlik's new 15 week course: Applied Data Analytics using Qlik Sense. READ MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
apthansh
Creator
Creator

Set Analysis

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

1 Solution

Accepted Solutions
sunny_talwar

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

View solution in original post

5 Replies
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
Creator
Creator
Author

Thank you Sunny.

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

sunny_talwar

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

View solution in original post

apthansh
Creator
Creator
Author

Awesome. Thank  YOU.

You are the best