Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Need help in Qlik Sense for Sum on Group By and Where Condition for percentage calculation. The VW appears only once Group by Tat_Qtr which for percentage calculation needs to be divided by every row of TXN_AMT_USD
Complete Expression:
(SUM({$<[Record_Type]={'Bklg','Bill'}>}[TXN_AMT_USD]) / <Need Expression> ) * 100
<Need Expression> for
Sum of TXN_AMT_USD IF record Type = 'WV' which appears only once for Group By TAT_Qtr
Snapshot_Date | Record_Type | Product | Product_Desc | TAT_YEAR | TAT_Qtr | TXN_AMT_USD |
5/4/2014 | Bklg | 03-25598-00 | SSS6203 ELP4 2004DN FOR ABC NA | 2014 | 2014Q4 | 518520 |
5/11/2014 | Bklg | 03-25598-00 | SSS6203 ELP4 2004DN FOR ABC NA | 2014 | 2014Q4 | 520550 |
5/18/2014 | Bklg | 03-25598-00 | SSS6203 ELP4 2004DN FOR ABC NA | 2014 | 2014Q4 | 474730 |
5/25/2014 | Bklg | 03-25598-00 | SSS6203 ELP4 2004DN FOR ABC NA | 2014 | 2014Q4 | 526060 |
6/1/2014 | Bklg | 03-25598-00 | SSS6203 ELP4 2004DN FOR ABC NA | 2014 | 2014Q4 | 184440 |
6/8/2014 | Bklg | 03-25598-00 | SSS6203 ELP4 2004DN FOR ABC NA | 2014 | 2014Q4 | 527800 |
6/15/2014 | Bklg | 03-25598-00 | SSS6203 ELP4 2004DN FOR ABC NA | 2014 | 2014Q4 | 661780 |
6/19/2014 | Bklg | 03-25598-00 | SSS6203 ELP4 2004DN FOR ABC NA | 2014 | 2014Q4 | 232290 |
6/19/2014 | WV | 03-25598-00 | SSS6203 ELP4 2004DN FOR ABC NA | 2014 | 2014Q4 | 2320000 |
Maybe like
(
SUM({$<[Record_Type]={'Bklg','Bill'}>}[TXN_AMT_USD])
/ SUM({$<[Record_Type]={'WV'}>} TOTAL<TAT_Qtr> [TXN_AMT_USD])
) * 100
Hi seuehl,
Thanks for your reply. This slightly helps but can you please suggested if nested level are more than one (as TAT_Qtr in this case).
For Example if i need Minimum of dates first on Level1 than on Level2 than on Level3 and a condition that if that date is minimum of Quarter (as Running Total will be zero for that first entry and than next all subsequent entries will be dependent on that output entry) which will be Grouped By say Level1 than on Level2 than on Level3.
Consider data below for above scenario.
nic
Report Filters | Org_Code | Part_Number | |||||
Org_Code | Part_Number | Customer_Part | Forecast_Dates | ||||
VCN | ACPM-5002-TR1 | 47100375 | |||||
8-Feb-16 | 15-Feb-16 | 22-Feb-16 | 29-Feb-16 | 7-Mar-16 | |||
Current Fcst | 2,254 | 662 | 402 | 402 | 603 | ||
Internal Orders | 0 | 0 | 0 | 10,000 | 0 | ||
Intransit Shipment Qty | 0 | 0 | 0 | 0 | 0 | ||
Projected On Hand | (2,254) | (2,916) | (3,318) | 6,280 | 5,677 | ||
VCN | ACPM-5004-TR1 | 47100360 | |||||
8-Feb-16 | 15-Feb-16 | 22-Feb-16 | 29-Feb-16 | 7-Mar-16 |