topic Re: Weekly Analysis in QlikView

Weekly Analysis

Mon, 21 Nov 2016 10:58:43 GMT

I am new to Qlikview and looking to find away to find the difference between forecasts on a weekly basis. I have found a few examples for monthly but not weekly.

I have figured out how to find the week of the year, so I can find the sum the totals for weeks (45 and 46) but cannot work out how to take one from the other. Although I do not know if this is the optimum method.

I load in the table as below

Forecast Date	Area	Sales

07/11/16	America	5
07/11/16	Europe	8
07/11/16	Asia	10
14/11/16	America	9
14/11/16	Europe	4
14/11/16	Asia	12

If I select Forecast date of 14/11/16 from a list box, I am looking to produce an output of

Area	Variance
America	4
Europe	-4
Asia	2

Any help which can be provided will be most appreciated.

Re: Weekly Analysis

Gysbert_Wassenaar — Mon, 21 Nov 2016 11:57:13 GMT

Perhaps like this:

sum({<[Forecast Date]={"$(=Date(Max([Forecast Date])-7,'DD/MM/YYYY')"}>}Sales)-sum(Sales)

Re: Weekly Analysis

sunny_talwar — Mon, 21 Nov 2016 12:01:56 GMT

I think you misplaced one parenthesis and also an additional square bracket

Sum({<[Forecast Date] = {"$(=Date(Max([Forecast Date])-7),'DD/MM/YYYY')"}]>}Sales) - Sum(Sales)

I think it should be this

Sum({<[Forecast Date] = {"$(=Date(Max([Forecast Date])-7,'DD/MM/YYYY'))"}>}Sales) - Sum(Sales)

Re: Weekly Analysis

Mon, 21 Nov 2016 12:30:36 GMT

Thank you your quick responses, when I enter this expression and select 14/11/16 as the Forecast date, I get a chart which has the values of:

-9 America

-12 Asia

-4 Europe,

Where as I would have been expecting:

4 America

2 Asia

-4 Europe

Not sure why these are all negative and larger than expected values.??

Re: Weekly Analysis

Gysbert_Wassenaar — Mon, 21 Nov 2016 12:54:18 GMT

Aha, so that's where it went. I lost that bracket earlier.

Re: Weekly Analysis

Gysbert_Wassenaar — Mon, 21 Nov 2016 12:55:44 GMT

That would happen if you use Forecast date as a dimension in your chart.

Re: Weekly Analysis

Mon, 21 Nov 2016 14:02:07 GMT

Thanks for your help Gysbert and Sunny.

I am trying to workout a few other pieces, so no doubt I will be back with more simplistic questions, it's good to know there are experts out there who are willing to share their knowledge.