Dear community,
I've gotten pretty far with developing a dashboard of mine, but I seem to be stuck at something:
From data side I am loading two tables in which I'm importing current and last week data.
I built a pivot table to compare some figures from the two datasets above.
Problem is with the comparison itself, I can't seem to "marry" the two together. I can't "lookup" the same category in the historical table dynamically, only if I specifically specify it with a if condition. Categories are the same between the two tables:
![JohnMcDoe_0-1591886103547.png JohnMcDoe_0-1591886103547.png](https://community.qlik.com/t5/image/serverpage/image-id/35372i1EE09D56E19E121B/image-size/large?v=v2&px=999)
The calculation formula I am using:
Sum({<LastFriday.CurrentStage = {'Assigned','Created'}>} if(LastFriday.MainCategory = MainCategory , [SalesValue1LastFriday]))
(Highlighted is the part that probably needs changing)
So in short, I need to match LastFriday.MainCategory (the MainCategory field from the LastFriday table) to the same field in the Pivot Table, which uses the non-historic MainCategory values). I tried to account for this with a simple if as seen above, but the data it spits is not accurate. If I manually specify the category:
Sum({<LastFriday.CurrentStage = {'Assigned','Created'}>} if(LastFriday.MainCategory = 'B' , [SalesValue1LastFriday]))
then it works flawlessly, it's just then applies it to every row, obviously.