topic Re: Average Sales based on month selection in QlikView

Average Sales based on month selection

Wed, 25 Nov 2020 16:16:04 GMT

Hi All,

I want to show YTD Sales Average for Dept1 in a pivot table with month selection as shown below:

Month	Sales	YTD Avg Sales
Jan-16	10	10
Feb-16	20	15
Mar-16	30	20
Apr-16	40	25
May-16	50	30
Jun-16	60	35
Jul-16	70	40
Aug-16	80	45
Sep-16	90	50
Oct-16	100	55

Please help me. Thanks in advance.

Re: Average Sales based on month selection

tresB — Mon, 16 Jan 2017 06:14:55 GMT

Replace your Dept dimension with Month like in attached sample.

Re: Average Sales based on month selection

Mon, 16 Jan 2017 06:42:37 GMT

Hi Tresco,

Thanks for the reply.

My chart is with Dept,Sales,YTD Sales.

For Dept1 and Jan-16 selection , I want to show YTD Sales 10,

For Dept1 and Feb-16 selection , I want to show YTD Sales 15,

For Dept1 and Mar-16 selection , I want to show YTD Sales 20,

For Dept1 and Apr-16 selection , I want to show YTD Sales 25.

Please help me !

Re: Average Sales based on month selection

tresB — Mon, 16 Jan 2017 06:49:55 GMT

Jack 061685 wrote:

Hi Tresco,

Thanks for the reply.

My chart is with Dept,Sales,YTD Sales.

For Dept1 and Jan-16 selection , I want to show YTD Sales 10,

And you are not selecting the month to see the result. I am a bit confused.

Re: Average Sales based on month selection

Mon, 16 Jan 2017 06:59:55 GMT

Yes. Selecting the month. But I want to show Dept dimension in the chart/table.

Re: Average Sales based on month selection

tresB — Mon, 16 Jan 2017 07:04:06 GMT

This is not what you want. Then what do you want it to be like with the same selection?

Re: Average Sales based on month selection

Mon, 16 Jan 2017 07:05:14 GMT

I want to show for Feb-16 selection,

Dept1, Sales 20, YTD Sales 15.

Re: Average Sales based on month selection

susovan — Mon, 16 Jan 2017 07:13:52 GMT

You can try this attachment,

Re: Average Sales based on month selection

tresB — Mon, 16 Jan 2017 07:16:56 GMT

Use Exp:

Aggr(Rangeavg(Above( sum({<Month>}Sales),0,RowNo())), Dept,Month)

Re: Average Sales based on month selection

Mon, 16 Jan 2017 07:18:30 GMT

Hi Susovan,

For Feb-16 your solution showing YTD Avg Sales 20. But it should show 15.

Re: Average Sales based on month selection

tresB — Mon, 16 Jan 2017 07:27:23 GMT

Have you tried : Aggr(Rangeavg(Above( sum({<Month>}Sales),0,RowNo())), Dept,Month) ?

Re: Average Sales based on month selection

susovan — Mon, 16 Jan 2017 07:32:01 GMT

Hi,

But I have checked that the value of YTD Avg Sales are same for both chart.

Please find the below screen shot for the ready reference

Re: Average Sales based on month selection

Mon, 16 Jan 2017 07:56:18 GMT

tresesco,Taking much time to calculate even with less data.Is there another way to do same calculation ?

Aggr(Rangeavg(Above( sum({<Month>}Sales),0,RowNo())),Dept,Month)