How to cross refer another table with aggr function in Qlik Sense

[Anonymous]

Hi,

Please note this I need it for Qlik Sense.

I am very new to Qlik Sense. I am stuck with below situation and don't know how to solve this. Any guidance and help is a great help.

Below are sample tables and the formula that I got so far.

Actual Cost table

Prod	Time	Cost	Qty
Prod1	T1	500	50
Prod1	T2	1500	100
Prod2	T1	2000	100
Prod2	T2	1250	50
Prod3	T2	6000	120

Now what I wanted to do is this:

I want to take avg unit cost difference at each Product level between T2 and T1 time periods and multiply with T2 period quantities at SKU level and sum it as 'Total cost increase $'. I wrote the below formula for that:

sum(aggr(((sum({<Time = {'T2'}>}Cost)/sum({<Time = {'T2'}>}Qty)) - (sum({<Time = {'T1'}>}Cost)/sum({<Time = {'T1'}>}Qty)))*(Sum({<Time = {'T2'}>}Qty)), Prod))

The output I am getting for this is: 750

This logic works as intended as long as a Product present in both the periods but Prod3 is completely omitted from the calculations, which I do not want. I have another unit cost reference tabel for T1 period for all the Products (including the ones that were not sold). Since in the above table Prod3 missing a transaction in T1 period, I want to use the avg unit cost of Prod3 in the place of

(sum({<Time = {'T1'}>}Cost)/sum({<Time = {'T1'}>}Qty)). I want to cross refer the unit cost table only when that product misses transaction in T1 period.

I am stuck with this logic and unable to think any solution that I can use. The sample Avg unit cost table is as below:

Prod	Time	Unit Cost
Prod1	T1	10
Prod2	T1	20
Prod3	T1	40

The final aggregation outcome I am looking to get in this example is 1950 (750 + 1200 where 1200 came from (50-40)*120 from Prod3). I do not want to use the Avg unit cost reference table for ALL the products. Use it only when the transaction is missing in T1 period.

Please let me know if you need any details that I missed to mention here.

Thanks in advance for the help.

Thanks & Regards,

Balaji

[sunny_talwar]

How are the two tables stored in your database? Are they linked or concatenated or joined?

[Anonymous]

Hi Sunny,

Currently these two tables are stored as separate tables and have association on Prod and Time fields.

Thanks,

Balaji

[sunny_talwar · Accepted solution]

May be you can do something like this

If(Len(Trim(Cost)) = 0, [Unit Cost], Cost)

Check if Cost is available or not, if it is not, then use Unit Cost

Or may be

If(Sum(Cost) = 0, Sum([Unit Cost]), Sum(Cost))

I have not added the set analysis to your expression, but you get the idea.... Add the set analysis back to the above expression

[Anonymous]

Thanks for the tip Sunny. I will check that.

Thanks,

Balaji

[Anonymous]

Thanks Sunny. It got me the desired output I am looking.

Thanks,

Balaji