Load as (new dimension) but between two dates using today()

[Daryn]

Hi everyone,

I can't seem to get the below working, I guess I am writing it in Qlik language rather than proper SQL?

I want to create a new dimension 'Aged Customers' which will contain a range of date ranges.

0-30

31-30

61-90

91-120

121+     etc etc ...   for the below I was just trying with the first couple of these to see if I could make it work)

This will be used to show aged customer values across those periods. 

Something like the below image (which is just an example I created of what I want it to look like)

So as part of my load script;

Date(Date#([FKDAT], 'YYYYMMDD') ) AS [FKDAT],
IF([FKDAT] < Today() AND [FKDAT] > (Today()-30),'0-30',
IF([FKDAT] < (Today()-30) AND [FKDAT] > (Today()-60),'31-60' )) AS [Aged Customers],

But that doesn't seem to work.

Any guidance/solution appreciated as always,

Regards Daryn

[Chanty4u]

May be try this 

LOAD

    Date(Date#([FKDAT], 'YYYYMMDD')) AS [FKDAT],

    IF([FKDAT] >= Today() - 30, '0-30',

        IF([FKDAT] >= Today() - 60, '31-60',

            IF([FKDAT] >= Today() - 90, '61-90',

                IF([FKDAT] >= Today() - 120, '91-120',

                    '121+'

                )

            )

        )

    ) AS [Aged Customers]

FROM

YourTable;

[Daryn]

Hi and many thanks for taking the time to read/respond.

I will try this now, but at first glance, will this only give me ranges from;

today -30 days   (ok)

today - 60 days (rather than between 31 and 60 days)

todays - 90 days  (rather than between 61 and 90 days) ???

Will apply and check back in a few.

Regards Daryn

[Daryn]

 Hi again, I am not getting the right results from that either, does load script not like today() ?

only seeing the 121+

Thanks in advance.

[Daryn]

Anyone, any other ideas? Many thanks again.

[_Iswarya_ · Accepted solution]

@Daryn 

Please refer the below link

Solved: how to create bucket for date - Qlik Community - 1939575

First. You need to ensure that you can interpret the date values as date. If they aren't date values then you can do this: date#([FKDAT], 'DD-MMM-YY')

Second  You need to identify your comparison date. I assume you want to compare with today's date  You can use today()

Third: To get the no of days between your [FKDAT]and your comparison date use subtraction, like this:

today () - date#([FKDAT], 'DD-MMM-YY')

Fourth. Bucket your date diff. You can do this using an nested if statement or using interval match. For this example I'll use an nestled if statement like this.

IF(today () - date#([FKDAT], 'DD-MMM-YY')<=30, '0-30',

• IF(today () - date#([FKDAT], 'DD-MMM-YY')<=60, '31-60', 

IF(today () - date#([FKDAT], 'DD-MMM-YY')<=90, '61-90',

      IF(today () - date#([FKDAT], 'DD-MMM-YY')<=120, '91-120',

'121+'))))

[Daryn]

Hey  _Iswarya_,

Huge thanks for taking the time to read and respond (and @Vegar  for the original post).

This is great, thanks. 

I do have another question, in understanding how each line relates to the specific period, in my mind;

IF(today () - date#([FKDAT], 'DD-MMM-YY')<=60, '31-60',            

PREVIEW

Would sum today to minus 60 days, not 31-60?    

I would expect it to need;

IF ((today ()-31) - date#([FKDAT], 'YYYYMMDD')<=60, '31-60',

Are you able to explain, is it that each following line of the bucket excludes the previous range or?

Thanks again and to the whole community, which is great and very helpful.

Daryn