Comparing previous periods if current period has no data

[#1]

Hi. I have a table where I'm displaying current period product sales and past periods product sales.

The records where the product has no current sales but had sales on past periods, are not displayed if the current period is selected.

Only if I unselect the current period, all records are displayed.

So in short, what I need, is to display all records, regardless if they were sold or not. Hint: The source of the data does not include records of products with no sales.

I tried selecting and unselecting those boxes to display null/zero values but nothing happened.

Thanks for you help.

[Miguel_Angel_Baeyens]

Hello Hans,

You will need to create those records in the script. Here you have a couple of good blog posts on how to do this by Henric Cronström:

• Relative Calendar Fields
• Generating Missing Data in QlikView

Hope that helps.

Miguel

[paulyeo11]

if you found a solution , pls share here.

[rwunderlich]

This is a common problem in period over period analysis. As an example, let's say today is July 15, 2013.

YTD is sum(Sales) through July 15, 2013.

Previous YTD is sum(Sales) through July 15, 2012.

What happens if you select Product=Bikes, and the last 2013 sale for bikes was June 20? The typical Set Analysis approach (max(Date)) would incorrectly (for most customers) calculate:

YTD = June 20, 2013

Previous YTD = June 20, 2012.

What is desired is the same date range when no products are selected -- that is, July 15 for both years. The syntax to do this right can get complicated. You shoud ignore the Product selection in calculating date, as well an any other selections that may impact the date. Fortunately, the latest (V7)  version of QVV (http://qlikviewcomponents.org) generates the correct expressions in it's Calendar routines. Only selections in the Master Calendar table are used to constrain the date range.

-Rob

[paulyeo11]

Hi Rob

Thank you for your detail explanation. this is the reasons using Flag can solve the problem.

By the way when i try to click on the link you point to us , it re-direct to below link :-

http://mercusuar.info/?url=qlikviiewcomponents.org

it is correct ?

[rwunderlich]

Sorry, I mispelled the link. I've corrected it in my post.

-Rob