Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Not applicable

How to add an avg distinct function with set analysis

Dear All,

Could someone please help me with below issue I am facing. need 4 expressions, but I am not able to succeed with the last one.

I added an attachment with the values and formula's I used.

I do a distinct count on Customer, so it gives me all unique customers per month.

In the 4th expression I want to show the average of these 12 months, so the total average of expression 3.

the average over 2014 for all unique customers is 134 (see example). I want to show this as a separate value in my 4th expression, so I can calculate deviations, etc. does anyone have an idea on how to do this?

Thanks for your help

3 Replies
Highlighted
Specialist III
Specialist III

include a total to you last set analysis

=count(total {<[Commodity]={"Chemicals"}, Year={2014} >} DISTINCT Customer)

Highlighted
Not applicable

HI Ramon,

Thanks for replying. But this formula gives me the total unique customers for 2014. and gives me value 347.

I am looking for the total average unique customers per month (even when a customer is booking each month). just like my example. the value should be 134 (average of the months 2014 as shown in expression 3).

Regards,

Bob

Highlighted
Master III
Master III

No offence intended, but wouldn't the average per month be a bit strange to show in a month column?

I would make a separate chart (Pivot or Straight) to show the years and commodities in and that chart have a column to show the average per year.

The expression for a chart like that would be like this:

= avg(aggr(count(distinct Customer), Month, Commodity))

Don't allow Perfection to be the enemy of Good Enough.
May you live in interesting times!