Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
How are the two tables stored in your database? Are they linked or concatenated or joined?
Hi Sunny,
Currently these two tables are stored as separate tables and have association on Prod and Time fields.
Thanks,
Balaji
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
Thanks for the tip Sunny. I will check that.
Thanks,
Balaji
Thanks Sunny. It got me the desired output I am looking.
Thanks,
Balaji