topic Re: How To segregate date from current date in QlikView

How To segregate date from current date

Anonymous — Fri, 03 Jan 2014 07:56:12 GMT

Hi All,

I am creating Bill wise total Outstanding amount I am having voucher date I need to segregate it from Current date by 0-30 days O/s(out Standing) and 30-60 days O/s, 60-90 O/s like that. how can I achieve it in Qlikview. Or else Kindly any one tell me how to segregate year quarter with that example I will modify it as per my requirement .

Re: How To segregate date from current date

Anonymous — Fri, 03 Jan 2014 08:45:31 GMT

just check this script

temp:

load * inline

[

Date ,amount

31/12/2013, 5000

15/12/2013 ,7000

3/11/2013 ,3000

15/11/2013 ,5000

27/11/2013 ,1000

2/10/2013 ,3000

];

LOAD

num(today(),'#####')- num(Date#(Date,'DD/MM/YYYY'),'#####') as num,

amount

resident temp;

drop Table temp;

then select a chart like a straight table go to dimension tab then there you see add calculated dimension

add below script

=IF(num>30,'30-60',

IF(num<30,'0-30'))

then go to expression tab of your chart add sum(amount) and you are done !!

anant

Re: How To segregate date from current date

svsudhakar — Fri, 03 Jan 2014 08:47:59 GMT

Hi ,

In script level u can calculate the date diff of today()- voucherdate

Ex:

if((Today()-voucherdate)>=1 or (Today()-voucherdate)<=30 ,'1-30',

if((Today()-voucherdate)>=31 or (Today()-voucherdate)<=60 ,'31-60',

regards,

sudhakar

Re: How To segregate date from current date

Anonymous — Fri, 03 Jan 2014 09:19:08 GMT

Hi Sudhakar,

This condition is not working for me it is showing 0 Values for when I sum my Outstanding amount after the condition

if((Today()-voucherdate)>=1 or (Today()-voucherdate)<=30 ,'1-30',Sum(TotalOutstanding))

I am having another doubt my date format is Like 08-OCT-2013 . But today() function is displaying like this 03-02-2014 because of this my condition is not working or what ??

Re: How To segregate date from current date

sujeetsingh — Fri, 03 Jan 2014 09:20:55 GMT

Use interval function

if(interval(today()-voucherdate,'d')>=1 or interval(today()-voucherdate,'d')<=30,'1-30')

Re: How To segregate date from current date

jagan — Fri, 03 Jan 2014 09:30:30 GMT

Hi Satish,

Try like this

Data:

LOAD

If(Days > 30 AND Days <= 60, '30-60',

If(Days > 0 AND Days <=30, '0-30', 'N/A'));

load *,

Interval(Today() - Date, 'D') AS Days

inline

[

Date ,amount

31/12/2013, 5000

15/12/2013 ,7000

3/11/2013 ,3000

15/11/2013 ,5000

27/11/2013 ,1000

2/10/2013 ,3000

];

Hope this helps you.

Regards,

Jagan.

Re: How To segregate date from current date

svsudhakar — Fri, 03 Jan 2014 09:41:53 GMT

ok then try with interval function