topic Re: Class with dates in QlikView

Class with dates

Tue, 06 Oct 2015 22:08:06 GMT

Hi Community:

I am trying to make a chart that looks like the following:

Here is my data. I am using the OPEN_QN_FLG field from the QN table and SRC_CREATE_DTTM field from the Squawk table.

The SRC_CREATE_DTTM is in number format and is the date that the quality notification (QN) was opened. The OPEN_QN_FLG field shows a 1 if the notification is still opened, and a 0 if it is closed. For my chart, I am only interested in the open QNs, or 1s.

This is the chart I am getting, and can't figure out how to fix it:

I commented out a lot of lines just because I have tried a lot of stuff and cannot get anything to work right. I put the num in front of TODAY so that it is in the same format as the SRC_CREATE_DTTM. Since the SRC_CREATE_DTTM has so many decimals, I used FLOOR in order to get rid of them.

I ideally do not want to change anything in the back end, if possible, because someone else is working on that as I design the front end. However, if that is the only option we can work together to add to that.

Thanks!

Re: Class with dates

Tue, 06 Oct 2015 22:09:35 GMT

Also, DaysOpen, in the CLASS function, is not a field, it is just the label that I used to name the classes

Re: Class with dates

Wed, 07 Oct 2015 12:31:56 GMT

Channing,

use this expression in calculated dimension

replace(class(num(today)-floor(SRC_CREATE_DTTM),30,'x',0),'x','-')

30 - interval

'-' default string

0 is offset value (starting point)

you have to use a function named Replace(string, fromstring, tostring) to display '-' between the range values on x - axis.

Re: Class with dates

Wed, 07 Oct 2015 13:37:00 GMT

Above is what changed. DaysOpen was replaced with a hyphen. That did help, but now I need the buckets to be different values (0-30 days, 30-60 days, 60-90 days, 90+ days)

Re: Class with dates

Anonymous — Wed, 07 Oct 2015 13:45:08 GMT

Hi Channing,

Please copy paste your calculated dimension here.

Re: Class with dates

Wed, 07 Oct 2015 13:46:56 GMT

use this for '-' only between the range

replace(class(num(today)-floor(SRC_CREATE_DTTM),30,'x',0),'<= x <','-')

Probably the minimum number of days between today and SRC_CREATE_DTTM is 420, that's why the range is getting started with 420.

Please share the qvw file for better understanding.

Re: Class with dates

Wed, 07 Oct 2015 13:47:48 GMT

=class(num(today())-floor(SRC_CREATE_DTTM),30, 'DaysOpen') is what I used originally

But then I changed it to what Anuj said, and then posted the new chart, after using =replace(class(num(today())-floor(SRC_CREATE_DTTM),30,'x',0),'x','-')

Re: Class with dates

Anonymous — Wed, 07 Oct 2015 13:54:39 GMT

Hi,

Use '<= x <' instead of 'x'

=replace(class(num(today())-floor(SRC_CREATE_DTTM),30,'x',0),'<= x <','-')

As for Buckets - Maybe you have date for starting 420 days and more.

Re: Class with dates

Wed, 07 Oct 2015 20:59:46 GMT

So I made a change. I ended up adding something to the back end. I added a field called AGE which subtracts the create date and today's date, so the column shows the number of days the quality notification has been opened. Now, my dimension is this: =replace(class(AGE,30,'x'),'<= x <','-') and that is labeled Age Group. My expression is sum(OPEN_QN_FLG).

I feel like I'm getting closer, but I only want 4 bars being 0-30, 30-60, 60-90, 90+.

How do I specify this?

Re: Class with dates

Wed, 07 Oct 2015 21:38:00 GMT

never mind! I figured it out

Re: Class with dates

MarcoWedel — Wed, 07 Oct 2015 22:04:52 GMT

Hi,

although you already solved it on your own, one solution might be also to combine the class() and the dual() functions to avoid sorting issues:

=If(Today()-Floor(SRC_CREATE_DTTM)>=90,Dual('90+',90),Dual(Replace(Class(Today()-Floor(SRC_CREATE_DTTM),30),'<= x <','-'),Class(Today()-Floor(SRC_CREATE_DTTM),30)))

hope this helps

regards

Marco

Re: Class with dates

beskqlik — Fri, 03 May 2019 10:51:03 GMT

I have same problem,

But i need this buckets with filter column with date. When i select this date from that date i should get past 90 days and bucketing should happen. Was your solution is with filter column.If yes, Can you please share it