Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
paulyeo11
Master
Master

How to make my display display 2 year instead of 12-24 month ?

Hi All


I have below script working fine :-


LOAD *,

applyMap('durationMap12', [No of Months_], '64 Mth and more') as Duration12_

;


Now it display in month, like 12-24 month , may i know how to make it display 2 year ?




Paul Yeo

1 Solution

Accepted Solutions
Anil_Babu_Samineni

If you are still looking in my way, Try to use

=If([No of Months_] >= 1 and [No of Months_] <= 24, Dual('0-2 Years', 1),

If([No of Months_] >= 24 and [No of Months_] <= 48, Dual('2-4 Years', 2),

If([No of Months_] >= 48 and [No of Months_] <= 72, Dual('4-6 Years', 3), Dual('More than 6 Years',4)

)))

If i think, In your way?

First do like

1) Promote field INVC SG tab

2) Ceil(Num(if(num(((date(today(),'DD/MM/YYYY') - date(@180:190,'DD/MM/YYYY'))/365*2))=0,1,num(((date(today(),'DD/MM/YYYY') - date(@180:190,'DD/MM/YYYY'))/365*2)))*24)) as  [No of Months_]

And, Reload and show the image how [No of Months_] looks like?

Best Anil, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful

View solution in original post

45 Replies
paulyeo11
Master
Master
Author

My QVW file.

Anil_Babu_Samineni

Thus [No of Months_] having only 12-24 months? If so, Please change from there it self to show 2 Years instead.

Best Anil, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful
paulyeo11
Master
Master
Author

Hi Anil

Thank you very much for your sharing.

My field [No of Months_]  script as below :-

Ceil(Num(if(num(((date(today(),'DD/MM/YYYY') - date(@180:190,'DD/MM/YYYY'))/365))=0,1,num(((date(today(),'DD/MM/YYYY') - date(@180:190,'DD/MM/YYYY'))/365)))*12)) as  [No of Months_],

How to change it ? so that it will display year ?

its_anandrjs
Champion III
Champion III

Try to use MakeDate() function which help you to create the date field

Makedate(Year,Month,Day) as NewDatefield

Anil_Babu_Samineni

May be this?

Ceil(Num(if(num(((date(today(),'DD/MM/YYYY') - date(@180:190,'DD/MM/YYYY'))/365*2))=0,1,num(((date(today(),'DD/MM/YYYY') - date(@180:190,'DD/MM/YYYY'))/365*2)))*24)) as  [No of Months_],

Best Anil, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful
paulyeo11
Master
Master
Author

Hi Anand

Thank you for your sharing.

I don't know how to start ?

Below is my script :-

Ceil(Num(if(num(((date(today(),'DD/MM/YYYY') - date(@180:190,'DD/MM/YYYY'))/365))=0,1,num(((date(today(),'DD/MM/YYYY') - date(@180:190,'DD/MM/YYYY'))/365)))*12)) as  [No of Months_],

How to replace above using below :-

Makedate(Year,Month,Day) as NewDatefield

Any advise ?

Paul

paulyeo11
Master
Master
Author

Hi Anil

Your approach seem to work . But then when i try it , it does not work.

As after i create the [No of Months_]

I still need to using below script to generate Duration1 :-

durationMap12:

MAPPING LOAD

rowNo() as key,

(((recno()-1)*12)+1)  & '-' & (((recno()-1)*12)+12) & ' Mth' as label    // org (12 for 1-12 mth) (3 for 1 to 3 mth)

AUTOGENERATE 5  //org (5 for 1-12 mth) ( 20 for 1-3 mth)

WHILE iterNo() <= 12   // org (12 for 1-2 mth) (3 for 1-3 mth)

;

LOAD *,

applyMap('durationMap12', [No of Months], '64 Mth and more') as Duration12

;

Any advise ?

Paul Yeo

Anil_Babu_Samineni

I haven't seen anything related No_of_Months_ in your script? Where do you apply?

Best Anil, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful
paulyeo11
Master
Master
Author

Hi Anil

I apply in Tab = Loop 12 , and Tab = Duration

You need to press control E.

Paul Yeo