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: 
madnanansari
Creator
Creator

Avg Sales per day - based on different days of work

I am working on Retail Sales data.

In some cities the stores works 7 days whereas in some they work 6 days a week.

So when I do an average sales per day for a month calculation it should take into account the operational days to calculate.  Let say for the month of January 2018; for Cities where the stores are closed on Sunday, the number of days is 27 whereas for other cities the number of days is 31.

I am using standard master calendar.

The below are the table formats:

Store:

Store

Store City

City Operational Days:

City

Operational Day. (Sat, Sun, Mon etc)

Sales:

Invoice No

Item

Store

Qty

Sales

I need the average Sales Per day

5 Replies
swuehl
MVP
MVP

I would expect to see a SalesDate field in Sales table.

If your Sales table show such a field, do you really have entries for a store on a date without sales?

If not, do you have an entry for each store on each sales date with either a value >0 or zero (which I assume is kind of unusual, to have no sales at all on a given date, but this might depend on your stores & business). In other words, are there any cases with no records for a store but operational on that date?

If your sales table shows a record per store and operational date, you should basically be fine.

Use something like

=Sum(Sales) / Count({<Store = {"*"}>} DISTINCT Date)

assuming a Store based dimension.

madnanansari
Creator
Creator
Author

Sales table have entries of sales. Not without sales. Neither it has entries for each date.

This will not work.

If u can play something with the master data? where its defined which cities are operating on which days.

swuehl
MVP
MVP

This means there is no time dimension in your sales table? How do you aggregate monthly sales value then?

madnanansari
Creator
Creator
Author

Date is there...but with figures for sales not empty lines.

madnanansari
Creator
Creator
Author

the average formula is to divide the sales by number of working days during the month for that store. (in your suggestions; if there is no sales on some days; the avg will show higher figures which is not correct).