Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
paulyeo11
Valued Contributor II

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

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

1 Solution

Accepted Solutions
prma7799
Honored Contributor III

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

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)

)))))))

8 Replies
paulyeo11
Valued Contributor II

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

My QVW

prma7799
Honored Contributor III

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

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
Valued Contributor II

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

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>

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

What this field contains L_move? I mean Date format?

Life is so rich, and we need to respect to the life !!!
paulyeo11
Valued Contributor II

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

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>

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

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),

Life is so rich, and we need to respect to the life !!!
paulyeo11
Valued Contributor II

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

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
Valued Contributor II

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

Hi PM

Your expression work fine.

Thank you

Paul Yeo

Community Browser