Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
asmithids
Partner - Creator II
Partner - Creator 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. 

1 Solution

Accepted Solutions
PradeepReddy
Specialist II
Specialist II

Please see the attachment.. it might be helpful

View solution in original post

4 Replies
maxgro
MVP
MVP

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

asmithids
Partner - Creator II
Partner - Creator II
Author

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
Specialist II
Specialist II

Please see the attachment.. it might be helpful

asmithids
Partner - Creator II
Partner - Creator II
Author

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;