Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
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

1 Solution

Accepted Solutions
Not applicable
Author

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

View solution in original post

10 Replies
swuehl
MVP
MVP

Try

=sum(Sales) / GetSelectedCount( year_month )

Not applicable
Author

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

swuehl
MVP
MVP

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
Author

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

swuehl
MVP
MVP

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
Author

I have uploaded now ☺

Not applicable
Author

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

swuehl
MVP
MVP

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
Author

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