How to display the variance between PY and CY with different time periods

[btrompetter]

Hello,

I must display the variance between the PY and CY in a diagram and compare only monday with monday.

My expression is

(

avg( {$< Date = { ">=06.01.2009 <=06.01.2009" } >} Sales) -
avg( {$< Date = { ">=05.01.2010 <=05.01.2010" } >} Sales)
)
/
avg( {$< Date = { ">=06.01.2009 <=06.01.2009" } >} Sales)

And the dimension in the diagram is the day. If I choose only one day qlik view displays no data

If I choose a period qlikview shows the days which are in both time periods.

Is it possible to show a dimension with the days of the current year and get the calculation of both years?

Thanks for help

regards

Bastian Trompetter

[#2]

I think you need to replace th dollar sign with a 1, so QlikView ignores selections. Then you would get the value you'd expect from that expression (with those hardcoded dates).

I don't know if you're going to be able to use Set Analysis to compare CY to PY unless you change (or already have) something in your dataset. In our dataset, we associate a date with the corresponding previous year date, so one record would have CY = 2/1/2010, PY = 2/2/2009 in one record. In another QlikView application, we load in the fact fields from the previous year separately, so for each record, we have CY Sales = ?, PY Sales = ?. That makes it easy to display CY and PY on the same chart.

The difficulty with doing CY and PY with Set Analysis, is that you can't really reference the dimension. I apologize if that was an unhelpful tangent.

[btrompetter]

The problem is I think that I must use set analysis, because my company is a retailer and it makes no sense to compare the same dates. I created a free calanandar table where the user selects the time period. It has the precalculate depending PY day. I t works in normal formulars that have no dimension.

Is it possible to create a calculated dimension?

Is there no other reatailer outside in the QlikView world which has the same problem and a solution?

[btrompetter]

If you have different tables for the CY and PY can you make a variance on different time periods?

[sjoelsona8]

NMiller - I like the idea of your approach. Would you please attach an example qvw? I would appreciate it.