topic Re: Set analysis in straight table in QlikView

Set analysis in straight table

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

Good day!

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

Data:

The "to be" table should looks like this:

The table "as is" now look like this:

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.

sunny_talwar — Thu, 12 Apr 2018 14:11:38 GMT

Try this

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

aisolomatin — Thu, 12 Apr 2018 14:25:55 GMT

Thanks, Sunny! It works