topic Re: AVG, SUM, Set Analysis, Pivot Table Headache in QlikView

AVG, SUM, Set Analysis, Pivot Table Headache

hschultz — Wed, 26 Sep 2012 12:58:41 GMT

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

Re: AVG, SUM, Set Analysis, Pivot Table Headache

nstefaniuk — Wed, 26 Sep 2012 13:15:08 GMT

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.

Re: AVG, SUM, Set Analysis, Pivot Table Headache

hschultz — Wed, 26 Sep 2012 13:29:30 GMT

Thank you

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

Because not using Set analysis counts it across all months

Re: AVG, SUM, Set Analysis, Pivot Table Headache

nstefaniuk — Wed, 26 Sep 2012 13:33:13 GMT

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

Re: AVG, SUM, Set Analysis, Pivot Table Headache

Anonymous — Wed, 26 Sep 2012 13:41:40 GMT

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

Re: AVG, SUM, Set Analysis, Pivot Table Headache

hschultz — Wed, 26 Sep 2012 13:58:58 GMT

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

I can do this tomorrow sometime.

Re: AVG, SUM, Set Analysis, Pivot Table Headache

Anonymous — Wed, 26 Sep 2012 14:18:01 GMT

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

Re: AVG, SUM, Set Analysis, Pivot Table Headache

hschultz — Mon, 15 Oct 2012 09:50:47 GMT

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.

Re: AVG, SUM, Set Analysis, Pivot Table Headache

kaushiknsolanki — Mon, 15 Oct 2012 09:56:31 GMT

Have a loo at the example.

Is this what you want.

Regards,

Kaushik Solanki

Re: AVG, SUM, Set Analysis, Pivot Table Headache

hschultz — Mon, 15 Oct 2012 10:32:47 GMT

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