topic Re: Create Zero Values On Daily Records When No Activity in QlikView

Create Zero Values On Daily Records When No Activity

asmithids — Wed, 13 Jan 2016 21:33:05 GMT

Hello,

I am reporting on individual store's daily sales across a number of stores. Besides showing each store's daily sales, I need to also show the stores that did not post any sales for that day as well.

The question is how can I show the stores with "zero" sales as I am showing sales for each store?

I have a following tables in the data model. The master calendar uses the functions Min(Date) and Max(Today()) functions in the script that creates a date everyday in the master calendar.

Fact Table

Date
Store
Sales

Master Calendar

Date
Month
Year

Thank you in advance for assistance on this issue.

Re: Create Zero Values On Daily Records When No Activity

maxgro — Wed, 13 Jan 2016 21:38:14 GMT

a script solution could be to add a record for every Store-Date without Sales (Sales null)

Re: Create Zero Values On Daily Records When No Activity

asmithids — Wed, 13 Jan 2016 22:57:58 GMT

maxgro,

The issue is that the database does not have records for the days where there are no sales for a particular store. Not sure how to evaluate each calendar day to add missing days and 'zero' sales amount for those stores that don't have sales.

Re: Create Zero Values On Daily Records When No Activity

PradeepReddy — Thu, 14 Jan 2016 10:46:50 GMT

Please see the attachment.. it might be helpful

Re: Create Zero Values On Daily Records When No Activity

asmithids — Thu, 14 Jan 2016 20:51:35 GMT

Thank you pradeep!!

I haven't had the time to check back with the Community. I was able to implemented the following script which is very similar to your solution. Hopefully, you can make use of this scripting at some point.

MinMaxDate:
Load Min(Date) as MinDate, Max(Today()) as MaxDate resident StoreData;
Let vMinDate = Peek('MinDate',-1,'MinMaxDate') - 1;
Let vMaxDate = Peek('MaxDate',-1,'MinMaxDate') ;
Drop Table MinMaxDate;

Load
'457' As Store,
Date,
0 As [Net Sales];

Load Date(recno()+$(vMinDate)) as Date Autogenerate vMaxDate - vMinDate;