topic Re: Load as (new dimension) but between two dates using today() in App Development

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

Daryn — Wed, 10 May 2023 09:11:59 GMT

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

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

Chanty4u — Wed, 10 May 2023 09:20:12 GMT

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;

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

Daryn — Wed, 10 May 2023 09:26:28 GMT

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

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

Daryn — Tue, 16 May 2023 06:52:58 GMT

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.

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

Daryn — Tue, 16 May 2023 06:54:07 GMT

Anyone, any other ideas? Many thanks again.

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

_Iswarya_ — Wed, 17 May 2023 10:29:43 GMT

@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+'))))

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

Daryn — Wed, 17 May 2023 07:51:54 GMT

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',

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