topic Re: Calculate the difference between two numbers and Group the product in QlikView

Calculate the difference between two numbers and Group the product

leale1997 — Wed, 25 Nov 2020 16:16:04 GMT

I have been trying to calculate the difference between two dates and group the numeric output in different ways. I have found a way that works but is completely slow and complicated.

Ideally I would like to

(1) create a field called 'Days Since Due' by subtracting [Today()] - [Due_Date].

(2) group the output of the above formula by '0 to 30', '30 to 60', '60 to 90', and '90+'.

I think this is what I want but I couldn't get it to work:

date(Date#([Date_Due],'MM/DD/YYYY'),'DD/MM/YYYY') as Date_Due_Converted_to_Date,
     num(date(Date#([Date_Due],'MM/DD/YYYY'),'DD/MM/YYYY') ) as Date_Due_Converted_to_Number,
     num(date(today(),'DD/MM/YYYY')) as Date_Today,
     num(date(Date#([Date_Due],'MM/DD/YYYY'),'DD/MM/YYYY') ) - num(date(today(),'DD/MM/YYYY')) as Days_Since_today,

if(Days_Since_today<0,
if((Days_Since_today<=-1 and Days_Since_today>=-30),'30',
if((Days_Since_today<=-31 and Days_Since_today>=-60),'30 to 60',
if((Days_Since_today<=-61 and Days_Since_today>=-90),'60 to 90','+90')))) as OpenDaysPastDue,

I expanded it to this which works but is slow because it's so complex:

if(num(date(Date#([Date_Due],'MM/DD/YYYY'),'DD/MM/YYYY') ) - num(date(today(),'DD/MM/YYYY'))<0,
if((num(date(Date#([Date_Due],'MM/DD/YYYY'),'DD/MM/YYYY') ) - num(date(today(),'DD/MM/YYYY'))<=-1
       and num(date(Date#([Date_Due],'MM/DD/YYYY'),'DD/MM/YYYY') ) - num(date(today(),'DD/MM/YYYY'))>=-30),'30',
if((num(date(Date#([Date_Due],'MM/DD/YYYY'),'DD/MM/YYYY') ) - num(date(today(),'DD/MM/YYYY'))<=-31
   and num(date(Date#([Date_Due],'MM/DD/YYYY'),'DD/MM/YYYY') ) - num(date(today(),'DD/MM/YYYY'))>=-60),'30 to 60',
if((num(date(Date#([Date_Due],'MM/DD/YYYY'),'DD/MM/YYYY') ) - num(date(today(),'DD/MM/YYYY'))<=-61
   and num(date(Date#([Date_Due],'MM/DD/YYYY'),'DD/MM/YYYY') ) - num(date(today(),'DD/MM/YYYY'))>=-90),'60 to 90','+90')))) as OpenDaysPastDue,

Re: Calculate the difference between two numbers and Group the product

sunny_talwar — Wed, 19 Dec 2018 17:09:32 GMT

The complexity should not lead to it's slowness... but in order to avoid the complexity... you can do a preceding load like this

LOAD *,
 If(Days_Since_today<0,
  If((Days_Since_today<=-1 and Days_Since_today>=-30),'30',
   If((Days_Since_today<=-31 and Days_Since_today>=-60),'30 to 60',
   If((Days_Since_today<=-61 and Days_Since_today>=-90),'60 to 90','+90')))) as OpenDaysPastDue;

LOAD Date(Date#([Date_Due],'MM/DD/YYYY'),'DD/MM/YYYY') as Date_Due_Converted_to_Date,
     Num(Date#([Date_Due],'MM/DD/YYYY')) as Date_Due_Converted_to_Number,
     Num(Today()) as Date_Today,
     Date#([Date_Due], 'MM/DD/YYYY') - Today() as Days_Since_today
FROM ....;

Re: Calculate the difference between two numbers and Group the product

sunny_talwar — Wed, 19 Dec 2018 17:13:49 GMT

But you can also do this

LOAD Date(Date#([Date_Due],'MM/DD/YYYY'),'DD/MM/YYYY') as Date_Due_Converted_to_Date,
     Num(Date#([Date_Due],'MM/DD/YYYY')) as Date_Due_Converted_to_Number,
     Num(Today()) as Date_Today,
     Date#([Date_Due], 'MM/DD/YYYY') - Today() as Days_Since_today,
     If((Date#([Date_Due], 'MM/DD/YYYY') - Today()) < 0,
     If(((Date#([Date_Due], 'MM/DD/YYYY') - Today()) >= -30), '30',
     If(((Date#([Date_Due], 'MM/DD/YYYY') - Today()) >= -60), '30 to 60',
     If(((Date#([Date_Due], 'MM/DD/YYYY') - Today()) >= -90), '60 to 90', '+90')))) as OpenDaysPastDue
FROM ....;

Re: Calculate the difference between two numbers and Group the product

leale1997 — Thu, 20 Dec 2018 17:18:35 GMT

I'm still kind of stuck on this one.

I am attaching a test file hoping it will help me describe my intent.

I would like to calculate the difference between two numbers. due_date - today()

And then take the product of that calculation and group it.

Between -1 and -30 = '30'

Between -31 and -60 = '60'

Between -61 and -90 = '60'

GT -90 = '90+'

Re: Calculate the difference between two numbers and Group the product

sunny_talwar — Thu, 20 Dec 2018 17:33:35 GMT

Would you be able to share the Excel file - Test Data.xlsx? Also, can you provide the expected output from the data you will provide in the Excel

Re: Calculate the difference between two numbers and Group the product

leale1997 — Thu, 20 Dec 2018 17:51:07 GMT

Attached is the excel file.

I would like the output of the field 'Days_Since_Today' to align with the output in field 'OpenDaysPastDue'.

'Days_Since_Today' is the calculated field. 'OpenDaysPastDue' is the grouped field. (30,60,90,90+)

Thanks for the assist :0)