Help with AGGR

[er_abhichandra]

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

[rubenmarin]

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

[er_abhichandra]

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

[kamielrajaram]

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

[rubenmarin]

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.

[er_abhichandra]

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

[kamielrajaram]

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