topic Re: date in QlikView

date

upaliwije — Sun, 26 Jul 2015 16:09:54 GMT

IF((NUM(date#(PAID_MON,'YYYY-MM'))-NUM(date#(INT_MON,'YYYY-MM')))>150,'>5 MONTHS',INT_MON)As INT_MONTH1;

I am using above syntax in my load script to calculate above 5 months record.But I get the following result

PAID_MONTH	INT_MONTH1
2015-07	>5 MONTHS
2015-07	2015-02
2015-07	2015-03
2015-07	2015-04
2015-07	2015-05
2015-07	2015-06
2015-07	2015-07
2015-07	Total

It gives 7 months may be due to the fact 28 days in Feb Month. Is there a way to rewrite the above syntax to get the following result

PAID_MONTH	INT_MONTH1
2015-07	>5 MONTHS
2015-07	2015-03
2015-07	2015-04
2015-07	2015-05
2015-07	2015-06
2015-07	2015-07
2015-07	Total

Pls help

Re: date

sunny_talwar — Sun, 26 Jul 2015 20:18:14 GMT

Is this what you are looking for?

Try the script:

Table:

LOAD *,

IF((Num(Month(date#(PAID_MON,'YYYY-MM')))-Num(Month(date#(INT_MON,'YYYY-MM'))))>4,'>5 MONTHS',INT_MON)As INT_MONTH1;

LOAD * Inline [

PAID_MON, INT_MON

2015-07, 2015-01

2015-07, 2015-02

2015-07, 2015-03

2015-07, 2015-04

2015-07, 2015-05

2015-07, 2015-06

2015-07, 2015-07

];

HTH

Best,

Sunny

Re: date

MarcoWedel — Sun, 26 Jul 2015 21:21:44 GMT

Hi,

another solution could be:

LOAD *,
    Ceil(Rand()*1000) as Amount,
    If((Year(PAID_MON)-Year(INT_MON))*12+(Month(PAID_MON)-Month(INT_MON))>4,Dual('>5 MONTHS',1),INT_MON) as INT_MONTH1;
LOAD Date#(PAID_MON,'YYYY-MM') as PAID_MON,
    Date#(INT_MON,'YYYY-MM') as INT_MON
Inline [
PAID_MON, INT_MON
2015-07, 2014-11
2015-07, 2014-12
2015-07, 2015-01
2015-07, 2015-02
2015-07, 2015-03
2015-07, 2015-04
2015-07, 2015-05
2015-07, 2015-06
2015-07, 2015-07
];

hope this helps

regards

Marco

Re: date

upaliwije — Mon, 27 Jul 2015 03:56:26 GMT

Dear Wedel,

Thanks a lot . I learned a lot from your example. Pls also explain me following line in your syntax

Ceil(Rand()*1000) as Amount,

Re: date

MarcoWedel — Mon, 27 Jul 2015 10:51:46 GMT

Hi,

you're welcome.

Ceil(Rand()*1000) as Amount,

only creates some random data I used in the bar chart.

This line is not required for this solution.

regards

Marco

Re: date

upaliwije — Mon, 27 Jul 2015 11:08:08 GMT

Thanks

Re: date

MarcoWedel — Mon, 27 Jul 2015 22:01:37 GMT

I forgot to mention:

you could get a similar result without scripting an additional field by defining dimension limits in your chart:

hope this helps

regards

Marco

Re: date

upaliwije — Tue, 28 Jul 2015 00:19:58 GMT

Thanks a lot for your advice. Can I please contact you through email whenever I want your help in respect of QV issue. That would be a great favour.