Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Qlik® Product Spotlight: Discover what’s possible. Get more from our products.
See for yourself. Register today.
virat_jb
Not applicable

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

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

ProdTimeCostQty
Prod1T150050
Prod1

T2

1500100
Prod2T12000100
Prod2T2125050
Prod3T26000120

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:

ProdTimeUnit Cost
Prod1T110
Prod2T120
Prod3T140


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


1 Solution

Accepted Solutions
sunny_talwar
Not applicable

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

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

5 Replies
sunny_talwar
Not applicable

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

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

virat_jb
Not applicable

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

Hi Sunny,

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

Thanks,

Balaji

sunny_talwar
Not applicable

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

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

virat_jb
Not applicable

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

Thanks for the tip Sunny. I will check that.

Thanks,

Balaji

virat_jb
Not applicable

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

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

Thanks,

Balaji