topic Re: Help with AGGR in QlikView

Help with AGGR

er_abhichandra — Mon, 05 Jan 2015 11:03:56 GMT

Hi,

I have a requirement where I have a list of distributors and their sales with YearQtrs. I want to display only those resellers who have sales in the currently selected quarter but not in the previous 8 quarters. I have resellers with negative sales which I want to filter out. Therefore If a distributor has a positive sales in any one of the previous 8 quarters I want to exclude that as well as the one with negative sales.

I am attaching a sample data.

Thanks in advance.

Abhinava

Re: Help with AGGR

rubenmarin — Mon, 05 Jan 2015 11:23:36 GMT

Hi Abhinava, I did it without aggr, in Script I added a Period Field:

LOAD [Analysis Year Quarter],

MakeDate(Left([Analysis Year Quarter], 4), Right([Analysis Year Quarter],1)*3) as Period,

WW_Reseller_Parent,

Sales

FROM

[.\Community Aggr.xlsx]

(ooxml, embedded labels, table is CH26_20150105_162714);

and then a simple table with WW_Reseller_Parent as Dimension and an expression like:

If(Sales, //Or "If(Sales>0" if you want to get only the ones with positive sales in selected quarter

If(Count({<Period={">=$(=AddMonths(Period, -24))<=$(=Period))"}, Sales={">0"}, [Analysis Year Quarter]>} DISTINCT [Analysis Year Quarter])=1, Sum(Sales))

)

Hope this helps

Re: Help with AGGR

er_abhichandra — Mon, 05 Jan 2015 11:29:42 GMT

Hi Ruben,

Thanks for the reply. I have tried this method but what I want differs from this. I want the sales to be aggregated for each distributor at YearQtr level and then filter out the negative bookings, not each individual rows of negative bookings.

For example,

2012 Q3 Dist1 56.6

2012 Q3 Dist1 -24.5

2012 Q4 Dist 1 -6.6

Then I want the aggregated sum to be considered for each Distributor and YearQtr. thus for my example for 2012 Q3 Dist1 should come up with 22.1 and for 2012 Q4 the -6.6 should be neglected

I hope this clears the confusion.

Thanks,

Abhinava

Re: Help with AGGR

kamielrajaram — Mon, 05 Jan 2015 11:39:06 GMT

Hello,

If based on selections, have you tried something like the below in your expression.

Aggr(Sum(If(Sales > 0,Sales, 0)),WW_Reseller_Parent,[Analysis Year Quarter])

Regards

Kamiel

Re: Help with AGGR

rubenmarin — Mon, 05 Jan 2015 12:16:28 GMT

Sorry I'm not sure to understand it right, you want something like?:

If(Sum(Sales)>0,

If(Sum({<Period={">=$(=AddMonths(Period, -24))<=$(=Period))"}, [Analysis Year Quarter]>}

Aggr(If(Sum({<Period={">=$(=AddMonths(Period, -24))<=$(=Period))"}, [Analysis Year Quarter]>}Sales)<>0, 1, 0), [Analysis Year Quarter], WW_Reseller_Parent))=1, Sum(Sales))

)

It takes the customers with positive sales, and with no sales in the previous 8 quarters of the selected.

Re: Help with AGGR

er_abhichandra — Mon, 05 Jan 2015 12:23:58 GMT

Hi Kamiel,

I want the following filter. First I want to get the aggregated bookings for the previous 8 qtrs. Assuming Period is autonumber of YearQtr.

Aggr(Sum({<Period={">=$(=MAX(Period)-8)<$(=MAX(Period))"}>}Sales),WW_Reseller_Parent,[Analysis Year Quarter])

Then I want to take only the zero sales in this aggregated table.

I am not able to compare each individual row of this aggregated table in memory to zero.

Is there any way I can do that?

Thanks,

Abhinava

Re: Help with AGGR

kamielrajaram — Tue, 06 Jan 2015 10:05:34 GMT

Hi Abhinava,

Sorry, but I am not to clear on what you want to achieve with comparing to zero. Hope this helps though.

Aggr(Sum({<WW_Reseller_Parent = , Period={">=$(=MAX(Period)-8)<$(=MAX(Period))"}>}if(Sales > 0,Sales) ),WW_Reseller_Parent,[Analysis Year Quarter])

Aggr(Sum({<WW_Reseller_Parent = , Period={">=$(=MAX(Period)-8)<$(=MAX(Period))"}>}if(Sales <= 0,Sales) ),WW_Reseller_Parent,[Analysis Year Quarter])

Regards

Kamiel