Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Master Calendar
Thank you in advance for assistance on this issue.
Please see the attachment.. it might be helpful
a script solution could be to add a record for every Store-Date without Sales (Sales null)
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.
Please see the attachment.. it might be helpful
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;