# QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
On May 18th at 10AM EDT we will answer your QlikView questions live. REGISTER
cancel
Showing results for
Search instead for
Did you mean:
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
MVP

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

5 Replies
MVP

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

MVP

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

Creator
Author

Thank you Sunny.

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

MVP

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

Creator
Author

Awesome. Thank  YOU.

You are the best