topic Re: Comparing previous periods if current period has no data in QlikView

Comparing previous periods if current period has no data

Wed, 24 Jul 2013 22:16:58 GMT

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.

Re: Comparing previous periods if current period has no data

Miguel_Angel_Baeyens — Wed, 24 Jul 2013 22:55:49 GMT

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:

Hope that helps.

Miguel

Re: Comparing previous periods if current period has no data

paulyeo11 — Thu, 25 Jul 2013 00:03:36 GMT

if you found a solution , pls share here.

Re: Comparing previous periods if current period has no data

rwunderlich — Thu, 25 Jul 2013 02:48:21 GMT

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

Re: Comparing previous periods if current period has no data

paulyeo11 — Thu, 25 Jul 2013 03:25:47 GMT

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 ?

Re: Comparing previous periods if current period has no data

rwunderlich — Thu, 25 Jul 2013 04:11:00 GMT

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

-Rob