Aggregation in qlikview

[prachisangewar]

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

0)

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!

[rubenmarin]

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.

[vinieme12]

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

[#4]

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

[prachisangewar]

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

[rubenmarin]

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

[effinty2112]

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