# QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

cancel
Showing results for
Did you mean:
Creator

## How to calculate weeks since first sale?

I am trying to calculate a KPI that will tell me, on average, how many units I sell per week.

I have my current Inventory, and how many units that are sold.

Here's what I want to do:

Units sold/number of weeks since first sold unit.

Units sold = Fsgantal

Honestly, I have no idea how to do this. I'm thinking some sort of RangeSum formula.

4 Replies
Creator III
Can you provide some sample data or your current project?
Creator
Author

Since I am quite lost in this calculation What I have now is a huge dataset with figures like this:

 Item Number 2019w01 2019w02 2019w03 2019w04 2019w05 Average Sale per Week 4030-450-56 0 0 2 0 3 1.6667 4030-451-55 5 6 7 0 0 3.6 3025-654-28 4 2 6 4 1 3.4

Here's how it looks if I just pull the raw data. I want to recreate the column "Average Sale per Week", but only count from the week where the first sale occurred. So For row 1, that would mean to calculate (2+0+3)/3 = 1.6666667

For the second row, it would take all figures into account, even if the item didn't had sales for all weeks, it was still "active" in our assortment. (5+6+7+0+0)/5 = 3.6

The third row, should account for all weeks, since it had sales on each separate week.

In my application the weeks are labeled "YearWeek" and Item Numbers are labeled "itemno". The units, or data itself, is labeled "Fsgantal".

I hope this helps, sorry I can't post any thought out formulas yet, I haven't gotten that far.

Creator III

Have a look at this.

Creator
Author
So:
=IF([2019w01]<>'0',SUM([2019w01]+[2019w02]+[2019w03]+[2019w04]+[2019w05])/5,
IF([2019w02]<>'0',SUM([2019w01]+[2019w02]+[2019w03]+[2019w04]+[2019w05])/4,
IF([2019w03]<>'0',SUM([2019w01]+[2019w02]+[2019w03]+[2019w04]+[2019w05])/3,
IF([2019w04]<>'0',SUM([2019w01]+[2019w02]+[2019w03]+[2019w04]+[2019w05])/2,
SUM([2019w01]+[2019w02]+[2019w03]+[2019w04]+[2019w05])))))

Thank you for your time, I appreciate it.
The problem is that the weeks changes and so does the range. And I have over 4,000 articles, and can't see myself writing 4,000 rows of what you just did 🙂