How To segregate date from current date - Qlik Community

Anonymous · ‎2014-01-03

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 .

Anonymous · ‎2014-01-03

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

svsudhakar · ‎2014-01-03

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

Anonymous · ‎2014-01-03

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 ??

sujeetsingh · ‎2014-01-03

Use interval function

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

jagan · ‎2014-01-03

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.

svsudhakar · ‎2014-01-03

ok then try with interval function