topic Spot inventory split into years in QlikView

Spot inventory split into years

Tue, 05 Oct 2010 22:22:34 GMT

Dear All,

I'm trying to create an expression that would show spot inventory like in the below table. The spot inventory is a sum of a given line value and all the values above it within a year, next year it starts over and show only the given line value.

Year, Month, Change, Spot inventory

2009, 10, 20, 20

2009, 11, 21, 41

2009, 12, 22, 63

2010, 1, 31, 31

2010, 2, -22, 9

2010, 3, 33, 43

Any idea how to do it?

Thanks,

Przemek

Spot inventory split into years

johnw — Tue, 05 Oct 2010 22:32:21 GMT

One way is an AsOf table:

AsOfMonth, InventoryType, Month
1, Spot, 1
1, Change, 1
2, Spot, 1
2, Spot, 2
2, Change, 2
3, Spot, 1
3, Spot, 2
3, Spot, 3
3, Change, 3
...
12, Spot, 11
12, Spot, 12
12, Change, 12

Then use a pivot table:

dimension 1 (left) = Year
dimension 2 (left) = AsOfMonth
dimension 3 (top) = InventoryType
expression = sum(Change)

So for an inventory type of 'Change', it only links to the current month, so you get only the current Change value. For an inventory type of 'Spot', it links to the current month and all previous months, so you get the sum of all Change values up to that month of the year.

Spot inventory split into years

syed_muzammil — Tue, 05 Oct 2010 22:38:22 GMT

Hi,

You can do this while loading the script itself. Sort the table on Year and Month (if you have date then sort it on date). Then add another feild which keeps on adding the previous records value only if the Years of current an previous records are same. this way you can directly use the new feild in your table

eg: if(Year=previous(Year), Previous(NewField)+Change) as NewField

Regards,

Syed.

Spot inventory split into years

Tue, 05 Oct 2010 22:44:30 GMT

Than you, John. It seems to be a solution but I was hoping for something using above() function or similar. Do you have an idea how to do it this way?

Spot inventory split into years

johnw — Tue, 05 Oct 2010 22:48:38 GMT

Syed's solution is simpler. I'd go with that. A couple slight revisions are required, I think. First, I think you need to use peek() to check the value of a field that isn't in your input table. Second, need to add an "else" to pick up the Change value in January:

if(Year=previous(Year), peek(NewField)+Change, Change) as NewField

Spot inventory split into years

johnw — Tue, 05 Oct 2010 22:53:09 GMT


przemek wrote:
Than you, John. It seems to be a solution but I was hoping for something using above() function or similar. Do you have an idea how to do it this way?<div></div>

Sure. Second expression, labeled 'Spot Inventory', this definition:

rangesum(Change,above("Spot Inventory"))

But I wouldn't do it that way. If you then select a single month, for instance, it will set the spot inventory to only that month's change value, because the above() is looking at your chart, not at your data. You really want it to look at data.