Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

asmithbi
Contributor II

Create Zero Values On Daily Records When No Activity

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. 

Tags (1)
1 Solution

Accepted Solutions
pradeepreddy
Valued Contributor II

Re: Create Zero Values On Daily Records When No Activity

Please see the attachment.. it might be helpful

4 Replies
MVP
MVP

Re: Create Zero Values On Daily Records When No Activity

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

asmithbi
Contributor II

Re: Create Zero Values On Daily Records When No Activity

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.    

pradeepreddy
Valued Contributor II

Re: Create Zero Values On Daily Records When No Activity

Please see the attachment.. it might be helpful

asmithbi
Contributor II

Re: Create Zero Values On Daily Records When No Activity

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;

Community Browser