AVG, SUM, Set Analysis, Pivot Table Headache

[hschultz]

Hi Guys

I really need some help because i don't know anymore....

We have to build

1. Pivot table

2. AVG of prices per line item

3. SUM for a grouped collection

4. Must be for a speciffic month.

So i am trying this:

>>>>

Avg({1<ML_DateFilter={$(vCurrDateTest)}>}(ML_BE_CostAmount/1))

<<<<

AND

>>>>

SUM({1<ML_DateFilter={$(vCurrDateTest)}>}(ML_BE_CostAmount/1))

/

COUNT({1<ML_DateFilter={$(vCurrDateTest)}>}(ML_BE_CostAmount))

<<<<

But in both cases we get the AVG as total when they are combined.

The reason is we have a few entries for the month, and SUM will multiply them (which we don't want) because a price of 100x4 now becomes 400 when we DO want the avg in that time of 100.

I know with a straight table you can easily select the Total Mode, but it is greyed out for Pivit Table, and always uses Expression Total.

Thank you in advance

[nstefaniuk]

Hi you can do it 2 ways:

If( count(distinct ....) = 1, <formula for unique item>, <formula for more than 1 item>)

Using aggr() that helps to do calculation on many levels, but a bit complex to demonstrate without an example.

[hschultz]

Thank you

How can i use count(distinct()) within Set Analysis?

Because not using Set analysis counts it across all months

[nstefaniuk]

If(count({$<...>}distinct item_id) =1, .., ...)

[Anonymous]

hi HS, an you post some of your sample data which will be easy for everyone to solve your problem.

[hschultz]

I must build a little sample, as i cannot sent the customer data.

I can do this tomorrow sometime.

[Anonymous]

yeah please do that you will get the perfect solution which you are looking for.

[hschultz]

Hi guys

Sorry it took so long to respond, but i have been crazy busy.

This now doesn't have the set analysis for the date, but it gives an example of what the query/issue is about.

[kaushiknsolanki · Accepted solution]

Have a loo at the example.

Is this what you want.

Regards,

Kaushik Solanki

[hschultz]

Yes! That is on the right track

I understand now. So i just pop my set analysis into the AVG calc like this:

sum(aggr(Avg({1<Date={'Oct'}>}(Value)),Area,Customer))

and it works 100%

Thank you very much