Hi,
First off - I am using Qlikview 7.52.
I have a problem when calculating average sales for a specific time period.
I have two tables - sales table and a time table.
Something like this:
Sales:
Datekey,
sales,
product;
Time:
Datekey,
workday, - which is a flag of 1 or 0 depending on if it is a workday or not.
week,
month;
Now in a straight table chart I want to do this
sum(sales)/sum(workday)
which works fine as long as I have sold something on each date in the selection.
However, if I choose to only look at one specific product in combination with one specific month and I only sold this product on 5 days out of 22 workdays - my sum(workday) will give me 5 as the result instead of 22 that is the total number of workdays for this month.
I do not want to work with totals for each month cause I want to be able to choose specific dates or weeks and only work with them so that will not help me.
The only solution I have found is to create rows in the sales-table for each possible date and product and use 0 as sales-figure - but that makes the total data size extremely big and it wont work in my application. So, I figured that it should be some easier way to make the connection between the two tables so that only the date choice is restriciting the time table but not the product choice. I have tried to work with otal and All in the chart but it doesnt seem to be of any help in this situation.
Any suggestions would be greatly appreciated.
Kind regards
Peter
