Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

paulyeo11
Valued Contributor II

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

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

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?

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

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

My QVW file.

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

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

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

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

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 ?

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

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

Makedate(Year,Month,Day) as NewDatefield

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

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_],

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

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

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

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

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

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

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

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

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

Hi Anil

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

You need to press control E.

Paul Yeo

Community Browser