Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Not applicable

Qlik Sense Sum on Group By and Where Condition

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_DateRecord_TypeProduct Product_DescTAT_YEARTAT_QtrTXN_AMT_USD
5/4/2014Bklg03-25598-00SSS6203 ELP4 2004DN FOR ABC NA20142014Q4518520
5/11/2014Bklg03-25598-00SSS6203 ELP4 2004DN FOR ABC NA20142014Q4520550
5/18/2014Bklg03-25598-00SSS6203 ELP4 2004DN FOR ABC NA20142014Q4474730
5/25/2014Bklg03-25598-00SSS6203 ELP4 2004DN FOR ABC NA20142014Q4526060
6/1/2014Bklg03-25598-00SSS6203 ELP4 2004DN FOR ABC NA20142014Q4184440
6/8/2014Bklg03-25598-00SSS6203 ELP4 2004DN FOR ABC NA20142014Q4527800
6/15/2014Bklg03-25598-00SSS6203 ELP4 2004DN FOR ABC NA20142014Q4661780
6/19/2014Bklg03-25598-00SSS6203 ELP4 2004DN FOR ABC NA20142014Q4232290
6/19/2014WV03-25598-00SSS6203 ELP4 2004DN FOR ABC NA20142014Q42320000
Tags (2)
2 Replies
MVP
MVP

Re: Qlik Sense Sum on Group By and Where Condition

Maybe like

(

SUM({$<[Record_Type]={'Bklg','Bill'}>}[TXN_AMT_USD])

/ SUM({$<[Record_Type]={'WV'}>} TOTAL<TAT_Qtr> [TXN_AMT_USD])

) * 100

Not applicable

Re: Qlik Sense Sum on Group By and Where Condition

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 FiltersOrg_CodePart_Number

Org_Code

Part_NumberCustomer_PartForecast_Dates
VCNACPM-5002-TR147100375
8-Feb-1615-Feb-1622-Feb-1629-Feb-167-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
VCNACPM-5004-TR147100360
8-Feb-1615-Feb-1622-Feb-1629-Feb-167-Mar-16