Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Not applicable

Calculate average pr month

Hi,

I want to visualise sales pr customer. One of the dimensions is year/month. To make the data comparable regardless of number of month in selection, I want to visualise the sales expressions as $/month. I've tried with sum(Sales)/count(distinct year_month), which almost gives me what I want. The problem is that if there were no sales to a customer a month, the count excludes that month, which I don't want it to do.

So I want to do someting like:

sum(Sales)/count(year_month "selected in year_month list box")

Any suggestions how to implement this expression?

Thanks in advance.

Regards,

Tom Arne Sivertsen

Tags (2)
1 Solution

Accepted Solutions
Not applicable

Re: Calculate average pr month

Summary:

  • sum(Sales) / count({<Customer, Product>} total distinct Month)
    • remember to include all dimensions except Month inside <>.

  • sum(Sales) / if(IsNull(GetFieldSelections(Month)),count({1} total distinct Month),(substringcount(GetFieldSelections(Month,';'),';')+1))
    • gives the correct result without having to specify all other dimensions. But is rather messy. Any suggestions for a simple expressions without specifying all other dimensions is appreciated.

Thanks to swuehl for helpful contribution!

- Tom Arne

10 Replies
MVP
MVP

Re: Calculate average pr month

Try

=sum(Sales) / GetSelectedCount( year_month )

Not applicable

Re: Calculate average pr month

Thanks, it helps a bit, but I see I was a little inaccurate in my description.

GetSelectedCount( year_month ) seems to return "active" selections (those green selections). But if GetSelectedCount( year_month ) = 0 (no date active selections are done), I need to get the count of the full span of dates in the list box (both white and grey).

In addition to that, I see that if I first choose months Jan, Feb & Mar (3 months), and then a customer without sales for e.g. Feb, GetSelectedCount( year_month ) returns 2, but I want 3 (just as the current selections box shows in this example (please see attached screen shot).

Any ideas of further functions to utilize?

Regards,

Tom Arne

CustomerSales.jpg

MVP
MVP

Re: Calculate average pr month

Maybe like this

=sum(Sales) / count({<Customer>} distinct Month)

You'll need to add all fields that might limit the Month selections in the set expression, comma separated, e.g. with an additional Product:

=sum(Sales) / count({<Customer, Product>} distinct Month)

Not applicable

Re: Calculate average pr month

Using "=sum(Sales) / count({<Customer>} distinct Month)",

it still returns 2 instead of 3 in my test app (with only month and customer as dimensions). Can I upload my example somewhere for you to have a look in what I mean?

-Tom Arne

MVP
MVP

Re: Calculate average pr month

Ah, sure, I forgot that Customer is / could be a dimension.

Upload is available from the advanced editor (link in editor in upper right corner, should be default when editing an existing post).

edit: and try

=sum(Sales) / count({<Customer, Product>} total distinct Month)

Not applicable

Re: Calculate average pr month

I have uploaded now ☺

Not applicable

Re: Calculate average pr month

Thank you! Now I get the correct numbers.

But do I need to specify the dimension(s)?

=sum(Sales) / count({1} total distinct Month)

seems also to return my expected number as well (and should be more robust...). Do you see any drawback by specifying {1} instead of the dimensions?

Regards,

Tom Arne

MVP
MVP

Re: Calculate average pr month

Well, it depends on your needs, {1} could be too robust. What if you select only Jan and Feb (two months)?

Do you then want your denominator to return 2 or 3 in your sample?

Not applicable

Re: Calculate average pr month

I see your point. I want 2 in that example, so {1} will be wrong.

I played a little around and came up with this messy version utilizing the function GetFieldSelections together with count {1} total:

 

=

if(IsNull(GetFieldSelections(Month)),count({1} total distinct Month),(substringcount(GetFieldSelections(Month

,';'),';')+1))

It seems to me to return the correct result without having to specify all possible dimensions. I'll give the two versions a try when my app evolves forward. Please let me know if you see a simpler way to calculate this without specifying all other dimensions than month.

Many thanks!

-Tom Arne

Community Browser