How to add L_move date < 01/01/2014 as new condition ?

[paulyeo11]

Hi All

My below expression working fine , as when i select >3-4 yr at Lsit 1 , it display 74,770 :-

=

if(L_issue = '00/00/0000' AND L_move <> '00/00/0000', ('New Product'),

If([No of Months_] >= 1 and [No of Months_] <= 6, Dual('<6 Mth', 1),

If([No of Months_] >= 7 and [No of Months_] <= 12, Dual('>6 Mth-1 Yr', 2),

If([No of Months_] >= 13 and [No of Months_] <= 24, Dual('>1-2 Yr', 5),

If([No of Months_] >= 13 and [No of Months_] <= 36, Dual('>2-3 Yr', 6),

If([No of Months_] >= 37 and [No of Months_] <= 60, Dual('>3-4 Yr', 8),

If([No of Months_] >= 61 and [No of Months_] <= 84, Dual('>4-5 Yr', 9),

If([No of Months_] >= 85 and [No of Months_] <= 908, Dual('>5 Yr', 10)

))))))))

Now i need to remove those last move date < 2014 ( dont display those date from 2015 jan till now) on to tha bove the above expression. So that the total amount will display 51.738K.

I try to modify the expression as below :-

=

=If([No of Months_] >= 1 and [No of Months_] <= 6, Dual('<6 Mth', 1),

If([No of Months_] >= 7 and [No of Months_] <= 12, Dual('>6 Mth-1 Yr', 2),

If([No of Months_] >= 13 and [No of Months_] <= 24, Dual('>1-2 Yr', 5),

If([No of Months_] >= 13 and [No of Months_] <= 36, Dual('>2-3 Yr', 6),

If([No of Months_] >= 37 and [No of Months_] <= 60 and L_move<="01/01/2014", Dual('>3-4 Yr', 8),

If([No of Months_] >= 61 and [No of Months_] <= 84, Dual('>4-5 Yr', 9),

If([No of Months_] >= 85 and [No of Months_] <= 908, Dual('>5 Yr', 10)

))))))))

But it does not work.

Hope some one can advise me.

Paul Yeo

[paulyeo11]

My QVW

[prma7799 · Accepted solution]

Try this...

=If([No of Months_] >= 1 and [No of Months_] <= 6, Dual('<6 Mth', 1),

If([No of Months_] >= 7 and [No of Months_] <= 12, Dual('>6 Mth-1 Yr', 2),

If([No of Months_] >= 13 and [No of Months_] <= 24, Dual('>1-2 Yr', 5),

If([No of Months_] >= 13 and [No of Months_] <= 36, Dual('>2-3 Yr', 6),

If([No of Months_] >= 37 and [No of Months_] <= 60 and L_move <= '01/01/2014', Dual('>3-4 Yr', 8),

If([No of Months_] >= 61 and [No of Months_] <= 84, Dual('>4-5 Yr', 9),

If([No of Months_] >= 85 and [No of Months_] <= 908, Dual('>5 Yr', 10)

)))))))

[paulyeo11]

Hi PM

Thank you for your sharing , I will try to night , as now I am out of my office.

Paul Yeo

DIrector

TDS Technology (S) P/L

Whatsapp +65 9326 1804

www.tdstech.com<http://www.tdstech.com>

[Anil_Babu_Samineni]

What this field contains L_move? I mean Date format?

[paulyeo11]

Yes L_move is date format.

Paul Yeo

DIrector

TDS Technology (S) P/L

Whatsapp +65 9326 1804

www.tdstech.com<http://www.tdstech.com>

[Anil_Babu_Samineni]

I don't have access your file now. So, I am assuming you could use

If([No of Months_] >= 37 and [No of Months_] <= 60 and L_move<=Date(MakeDate(2014),'Your L_move format'), Dual('>3-4 Yr', 8),

But, Qlik and SQL engine may not known of your And / Or operator to work which is first. To work you may need this?

If(([No of Months_] >= 37 and [No of Months_] <= 60) and L_move<=Date(MakeDate(2014),'Your L_move format'), Dual('>3-4 Yr', 8),

[paulyeo11]

Hi Anil

I will try to night.

Paul Yeo

DIrector

TDS Technology (S) P/L

Whatsapp +65 9326 1804

www.tdstech.com<http://www.tdstech.com>

[paulyeo11]

Hi PM

Your expression work fine.

Thank you

Paul Yeo