topic Re: Calculate average pr month in QlikView

Calculate average pr month

Wed, 15 Aug 2012 09:58:48 GMT

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

Re: Calculate average pr month

swuehl — Wed, 15 Aug 2012 10:18:52 GMT

Try

=sum(Sales) / GetSelectedCount( year_month )

Re: Calculate average pr month

Wed, 15 Aug 2012 10:58:54 GMT

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

Re: Calculate average pr month

swuehl — Wed, 15 Aug 2012 11:24:35 GMT

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)

Re: Calculate average pr month

Wed, 15 Aug 2012 12:00:36 GMT

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

Re: Calculate average pr month

swuehl — Wed, 15 Aug 2012 12:03:03 GMT

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)

Re: Calculate average pr month

Wed, 15 Aug 2012 12:04:14 GMT

I have uploaded now ☺

Re: Calculate average pr month

Thu, 16 Aug 2012 06:48:27 GMT

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

Re: Calculate average pr month

swuehl — Thu, 16 Aug 2012 09:01:10 GMT

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?

Re: Calculate average pr month

Thu, 16 Aug 2012 10:20:28 GMT

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

Re: Calculate average pr month

Tue, 21 Aug 2012 06:45:15 GMT

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