topic Re: Monthly Average - Stumped in QlikView

Monthly Average - Stumped

prees959 — Mon, 16 Jul 2018 09:34:55 GMT

Hi,

My client would like me to show the monthly average sales before a 'Go Live' date and also the average monthly sales after the Go Live Date as a single bar chart with dimensions of PreGoLive and PostGoLive.

I have a [Pre Go Live] field which is set to 1 for Pre Go-Live or 0...

My measure field is [Total Sales].

I also have a master calendar with [Date], and also [Month]

Any help appreciated,

Phil

Re: Monthly Average - Stumped

thevingo — Mon, 16 Jul 2018 10:14:14 GMT

I know its easy but I cant figure-it-out exactly

1. Create a flag based on your Go-Live date as After & Before, or simply as A & B.

2. Use this in SET analysis to create the 2 expressions/measures.

3. Monthly average can be calculated as (Sales for Flag=A) / (No of MonthYears for Flag=A).

Hope it helps

Re: Monthly Average - Stumped

prees959 — Mon, 16 Jul 2018 13:00:29 GMT

thanks!

Re: Monthly Average - Stumped

sumit_ranjan_pa — Mon, 16 Jul 2018 13:04:41 GMT

Govind's Solution is optimised. That will work for sure. ALternatively, if you want make it dynamic, you can use set analysis and compare the Go live date to the current dates. This sounds bit complicated if you do not have any flags.

If you can create flags then it will be easier.

Re: Monthly Average - Stumped

prees959 — Tue, 17 Jul 2018 05:53:54 GMT

Hi Thanks both for your replies.

Just one more question. I have a flag that denotes the first 3 months of Go-Live ..it possible to create a monthly average of these three months? Also, I would like to compare it to the monthly average of the previous 3 full months I also have a flag that denotes these months... is this possible?

Re: Monthly Average - Stumped

sumit_ranjan_pa — Tue, 17 Jul 2018 07:15:09 GMT

Hi Phil,

Yes you can do that. They don't need any extra flags. You can create a monthly average on the date field.

This would be something similar to this.

Month ={Month(GoLiveDate)-3}

This would be for the first month. Then for second it would be

Month ={Month(GoLiveDate)-2}

last:

Month ={Month(GoLiveDate)-1}

This would be added as set analysis and have average for months by months.