Weekly Analysis

[#1]

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.

[Gysbert_Wassenaar]

Perhaps like this:

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

[sunny_talwar · Accepted solution]

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)

[#4]

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.??

[Gysbert_Wassenaar]

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

[Gysbert_Wassenaar]

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

[#7]

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.