Transforming data in AsOf table

[sergeyko]

Hi there,

I've created an AsOf table to comply with my use case of calculating a month-over-month ratio - see the link below for more details:

https://community.qlik.com/t5/New-to-Qlik-Sense/Trying-to-calc-month-over-month-ratio-of-current-vs-...

A twist in the ratio I need to calculate is that it is not just current / past, but the current must only include items that existed in the past month and sum their prices capped to the prices of the same items from the previous month. 

My data looks like as follows:

My ratio for Jan 2017 looks like (sum of past month) - (limited sum of current month) / (sum of past month)

Hence, (160+240+400) - (150+240[price of item 125 is capped to its previous amount]+0[item 135 is missing in past]) / (160+240+400)

I think I need to transform my data to introduce a couple of new columns:

a) The one that will say whether the current item has its duplicate in the past month

b) The other that will store capped prices of items

I would appreciate your suggestion on how to make those transformations on the data loading stage. If you have ideas how to make it work on the frontend, I am certainly open to them as well.

YearMonth	YearMonth_AsOf	TableFlag	Item #	Price
Jan 2017	Jan 2017	Current	123	$150
Jan 2017	Dec 2016	AsOf	123	$160
Jan 2017	Jan 2017	Current	125	$250
Jan 2017	Dec 2016	AsOf	125	$240
Jan 2017	Jan 2017	Current	135	$350
Jan 2017	Dec 2016	AsOf	140	$400
...	...	...	...	...

[Gysbert_Wassenaar]

Perhaps like this:

rangemin(sum({<TableFlag={'Current'}>}Price),sum({<TableFlag={'AsOf'}>}Price))/ sum({<TableFlag={'AsOf'}>}Price) - 1

[sergeyko]

Hi Gysbert,

Thanks for your input. As far as I understand, your set expression finds the minimum sum between Current as AsOf, whereas my challenge is to find the capped sum of Current based on AsOf. I tried my best to explain the logic and provide some examples in the post above.

My thinking is that kind of calculation is complicated enough to be pushed to backend. I'm currently trying to build a transposed AsOf table, where prices from previous months are put in new columns. This would open up a space for easy data manipulations. 

[Gysbert_Wassenaar]

You'll have to explain that capped thing in more detail to me, because I don't understand why for item 123 it's 150 and for item 125 it's 240.

[sergeyko]

The "price cap" logic is pretty straightforward in theory - if an item's price from Current is lower than or equal to the price of the same item from AsOf, take the price from Current; otherwise, take the price from AsOf.

This way, items' prices can go down but never go up comparing to the past.

[Gysbert_Wassenaar]

Ok, so the rangemin function takes the lowest of the two and that's wrong how then?

[sergeyko]

If I'm not mistaken, the rangemin would return the lowest of the two sums, which yields a correct result in some cases but does not work for all the scenarios. Let me make a few examples:

Case A: Items 1 and 2 have current prices of 100 / 200 and AsOf prices of 50 / 100. The rangemin sum is 150, and it matches the right answer, which in this particular case is the lowest sum.

Case B: Items 1 and 2 have current prices of 100 / 200 and AsOf prices of 50 / 400. The rangemin sum is 300, however the right answer is 50 (the lowest price) + 200 = 250.

Case C: Items 1 and 3 have current prices of 100 / 200, and AsOf there were items 1 and 2 with prices 150 and 50. The rangemin sum is 200, wherease the right answer is 100 (the lowes price) + 200 (the price of new item) = 300.

[Gysbert_Wassenaar]

Yes, you're right. I was assuming the Item would be used as a dimension. It doesn't work if that's not the case without adding another level of aggregation.

[sergeyko]

I'm glad we are on the same page now,

Would you have any ideas on how to transform my data on backend so that it is prepared for some easy calculation through set expressions?

If I find a way to identify new assets and assign old assets with capped prices in Current, that should allow for calculating my ratio pretty seamlessly over the year. 

[Gysbert_Wassenaar · Accepted solution]

I think you'll need to sort your fact table by item and month and then create the extra field with the capped price with something like If(Previous(Item)=Item, RangeMin(Peek(CappedPrice),Price),Price) as CappedPrice