Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
aisolomatin
Contributor III
Contributor III

Set analysis in straight table

Good day!

I need to calculate Sum(Sales) by quarters, excluding sales in last month each quarter.

Data:

data.png

The "to be" table should looks like this:

To be.png

The table "as is" now look like this:

as is.png

I can estimate MaxMonth in each row, but for Sum(Sales) expression it doesn't fit.

Expr. for maxMonth:

Max(Month_numberReport)

Expr. for Sales(no last month):

Sum({<Month_numberReport=e({<Month_numberReport={$(=Max(Month_numberReport))}>}Month_numberReport)>}Sales)


Is there any ideas?


Thank you.

1 Solution

Accepted Solutions
sunny_talwar

Try this

=Sum(Aggr(If(DateReport <> Max(TOTAL <QuarterReport>DateReport), Sum(Sales)), QuarterReport, DateReport))

View solution in original post

2 Replies
sunny_talwar

Try this

=Sum(Aggr(If(DateReport <> Max(TOTAL <QuarterReport>DateReport), Sum(Sales)), QuarterReport, DateReport))

aisolomatin
Contributor III
Contributor III
Author

Thanks, Sunny! It works