Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
Appreciate if I am informed the steps to be followed, when a subtotal wont come for a calculated dimension! Please see below the data.
The total comes for last two columns are in correct.The formula used to get the subtotal of both new & renewals are too given below.
=IF(IF([Year(MakeDate('20'&(MID( POLICY_NO,5,2))))]<'2015','RENEWAL',IF([Year(MakeDate('20'&(MID( POLICY_NO,5,2))))]='2017','NEW',IF((Num(CR_MONTH)+365)-Num(RISK_MONTH)>365,'RENEWAL','NEW')))='RENEWAL',SUM(PREMIUM)*-1)
=IF(IF([Year(MakeDate('20'&(MID( POLICY_NO,5,2))))]<'2015','RENEWAL',IF([Year(MakeDate('20'&(MID( POLICY_NO,5,2))))]='2017','NEW',IF((Num(CR_MONTH)+365)-Num(RISK_MONTH)>365,'RENEWAL','NEW')))='NEW',SUM(PREMIUM)*-1)
Thanks
Neville
Issue solved!
Thanks Sunny,
That worked well. Thanks once again.
Neville
Try these guys
=Sum(Aggr(
IF(IF([Year(MakeDate('20'&(MID( POLICY_NO,5,2))))]<'2015','RENEWAL',IF([Year(MakeDate('20'&(MID( POLICY_NO,5,2))))]='2017','NEW',IF((Num(CR_MONTH)+365)-Num(RISK_MONTH)>365,'RENEWAL','NEW')))='RENEWAL',SUM(PREMIUM)*-1)
, CLASS_CODE, CR_MONTH, POLICY_NO, REASON, RISK_MONTH))
=Sum(Aggr(
IF(IF([Year(MakeDate('20'&(MID( POLICY_NO,5,2))))]<'2015','RENEWAL',IF([Year(MakeDate('20'&(MID( POLICY_NO,5,2))))]='2017','NEW',IF((Num(CR_MONTH)+365)-Num(RISK_MONTH)>365,'RENEWAL','NEW')))='NEW',SUM(PREMIUM)*-1)
, CLASS_CODE, CR_MONTH, POLICY_NO, REASON, RISK_MONTH))
Issue solved!
Thanks Sunny,
That worked well. Thanks once again.
Neville
Great, please close this thread by marking correct and helpful responses.
Qlik Community Tip: Marking Replies as Correct or Helpful
Best,
Sunny