topic Re: Aggregation in qlikview in QlikView

Aggregation in qlikview

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

Hi,

I have a requirement where in I need to aggregate my measure at a lower granular level,and display at a higher level based on a condition.

Attached is a sample computation.

Please suggest if it is Possible to do in Qlikview

The Formula I am using in my qlikview Pivot table is :

=IF(sum(AGGR(SUM(M1+ M2 + M3 + M4),Customer))> 0,

sum(AGGR(SUM(M1+ M2 + M3 + M4),Customer)),

I also tried this,

=IF(SUM(AGGR(SUM(M1),Customer)+ AGGR(SUM(M2),Customer) + AGGR(SUM(M3),Customer) + AGGR(SUM(M4),Customer)) >0,

SUM(AGGR(SUM(M1),Customer)+ AGGR(SUM(M2),Customer) + AGGR(SUM(M3),Customer) + AGGR(SUM(M4),Customer)),0)

Thank you!

Re: Aggregation in qlikview

rubenmarin — Thu, 28 Jul 2016 11:44:23 GMT

Hi prachi, right now that expression is checking if the sum of all customers is greater than 0, show all, else show 0.

If you want to exclude some customers you can check each customer inside the Aggr():

=Sum(AGGR(If(SUM(M1+ M2 + M3 + M4)>0, SUM(M1+ M2 + M3 + M4), 0),Customer))

Using Sum with not numeric values will cause the sum to fail, to avoid this possibility you can use RangeSum(), wich converts not numeric values to zero:

=Sum(AGGR(If(RangeSum(M1, M2, M3, M4)>0, RangeSum(M1, M2, M3, M4), 0),Customer))

Not tested, maybe there is a typo.

Re: Aggregation in qlikview

vinieme12 — Thu, 28 Jul 2016 11:48:03 GMT

try SUM(TOTAL <Customer> M1+M2+M3+M4)

use this to evaluate by dept

sum(aggr(if(sum(TOTAL <Customer> M1+M2+M3+M4)>0,sum(TOTAL <Customer> M1+M2+M3+M4),0),Customer,Dept))

Re: Aggregation in qlikview

Thu, 28 Jul 2016 12:42:26 GMT

Hi Prachi,

Do the following to achieve your requirement,

Straight Table Properties,

Dimension: Dept

Expression: Aggr(Sum(Aggr(If(Sum([M1+M2+M3+M4])>0,Sum([M1+M2+M3+M4]),0), Customer)),Dept)

this 'll give you the table with 3 records which you need

Thanks,

Narsi

Re: Aggregation in qlikview

prachisangewar — Wed, 03 Aug 2016 08:55:57 GMT

Hi Experts,

Got Another issue with my report.I need to use the same logic but this time to compute Year to Date Amount.My formula is as below:

=(SUM(AGGR(IF((SUM({<Date=,DATE_NUM={">=$(=Num(YearStart(Max(DATE_NUM))))<=$(=Max(DATE_NUM))"}>}M1)

+SUM({<Date=,DATE_NUM={">=$(=Num(YearStart(Max(DATE_NUM))))<=$(=Max(DATE_NUM))"}>}M2)) = 0,

IF((SUM({<Date=,DATE_NUM={">=$(=Num(YearStart(Max(DATE_NUM))))<=$(=Max(DATE_NUM))"}>}M3)

+SUM({<Reporting_Date=,DATE_NUM={">=$(=Num(YearStart(Max(DATE_NUM))))<=$(=Max(DATE_NUM))"}>}M4)) > 0,

(Sum({<Reporting_Date=,DATE_NUM={">=$(=Num(YearStart(Max(DATE_NUM))))<=$(=Max(DATE_NUM))"}>}M3)

+ Sum({<Reporting_Date=,DATE_NUM={">=$(=Num(YearStart(Max(DATE_NUM))))<=$(=Max(DATE_NUM))"}>} M4)),0)),Customer,Dept)))

The requirement is to get the total amount form the beginning of the year to Date selected in the List box.

The issue is, if a Customer does not exist on the selected date but exists in the past days ,its values are not getting included in the final amount

e.g:

Customer A exists for Jan,Feb,March but not the Date selected by user (31st April),then the values of the three months are not taken into consideration.

Only if Customer exists on the Date selected all past values are taken into consideration..

Please suggest what should be solution to get all the values to be considered for each customer as it is Year to Date Report.

Thanks!!

Re: Aggregation in qlikview

rubenmarin — Wed, 03 Aug 2016 11:58:13 GMT

Hi prachi, maybe adding the set analisys to the first Sum():

=(SUM({<Date=,DATE_NUM={">=$(=Num(YearStart(Max(DATE_NUM))))<=$(=Max(DATE_NUM))"} AGGR(IF((SUM({<Date=,DATE_NUM={">=$(=Num(YearStart(Max(DATE_NUM))))<=$(=Max(DATE_NUM))"}>}M1)

+SUM({<Date=,DATE_NUM={">=$(=Num(YearStart(Max(DATE_NUM))))<=$(=Max(DATE_NUM))"}>}M2)) = 0,

IF((SUM({<Date=,DATE_NUM={">=$(=Num(YearStart(Max(DATE_NUM))))<=$(=Max(DATE_NUM))"}>}M3)

+SUM({<Reporting_Date=,DATE_NUM={">=$(=Num(YearStart(Max(DATE_NUM))))<=$(=Max(DATE_NUM))"}>}M4)) > 0,

(Sum({<Reporting_Date=,DATE_NUM={">=$(=Num(YearStart(Max(DATE_NUM))))<=$(=Max(DATE_NUM))"}>}M3)

+ Sum({<Reporting_Date=,DATE_NUM={">=$(=Num(YearStart(Max(DATE_NUM))))<=$(=Max(DATE_NUM))"}>} M4)),0)),Customer,Dept)))

Re: Aggregation in qlikview

effinty2112 — Wed, 03 Aug 2016 12:33:45 GMT

Hi Prachi,

Try:

Dept	Sum(Aggr(if(Sum(RangeSum(M1,M2,M3,M4))>0,Sum(RangeSum(M1,M2,M3,M4)),0),Customer))
D1	99
D2	162
D3	0

Good Luck

Andrew