topic Re: Set analysis: compare two date fields in QlikView

Set analysis: compare two date fields

florian_kloster — Tue, 20 Nov 2018 12:41:55 GMT

Hi everybody,

I am struggling creating a set analysis using two date fields.

What I want to do is to calculate the difference (days) between two fields but only for those cases where the one value is greater than the other.

avg({$<CREATED_DATE= {"=CREATED_DATE >= DATE"}>} CREATED_DATE - DATE)

In my case, for all cases were the created date is later than the date, I want to calculate the difference.

But it is not possible to get this working.

Best,
Florian

Re: Set analysis: compare two date fields

sunny_talwar — Tue, 20 Nov 2018 12:48:37 GMT

Are CREATED_DATE and DATE coming from the same table in your data model? Have you tried this with If statement?

Avg(If(CREATED_DATE >= DATE, CREATED_DATE - DATE))

You can also try this

Avg(RangeMax(CREATED_DATE - DATE, 0))

Re: Set analysis: compare two date fields

florian_kloster — Tue, 20 Nov 2018 12:56:45 GMT

Hi 🙂

yes, both fields are in the same table. I could use the if condition as well, but is it possible to realize it using set analysis?
Someone told me that this would save resources.

Thanks for your thoughts,
Florian

Re: Set analysis: compare two date fields

sunny_talwar — Tue, 20 Nov 2018 13:02:33 GMT

If you want to use set analysis... I would suggest you to create a flag in the script (since they are coming from the same table in the script)

If(CREATED_DATE >= DATE, 1, 0) as Flag,
CREATED_DATE - DATE as DATE_DIFF

and then use this

Avg({<Flag = {'1'}>} DATE_DIFF))

Re: Set analysis: compare two date fields

florian_kloster — Tue, 20 Nov 2018 13:06:36 GMT

Hi, yes this is a feasible solution. It was mentioned in other posts in the past.

But I would like to avoid creating unnecessary fields in my database - especially as I would like to create such a funnel view for more steps (8 steps in the funnel).

So am I right assuming that there is NO way to realizes this using set analysis without creating a flag field in the script?

Thanks,
Florian

Re: Set analysis: compare two date fields

sunny_talwar — Tue, 20 Nov 2018 13:29:09 GMT

1) You are looking to optimize yet you are hesitant of creating a flag field which will only take 1 or 0 value? Not sure I understand this.... if you go through the below thread, you will see that a flag field which store 1 or 0 should not have a huge impact on your data model.

Symbol Tables and Bit Stuffed Pointers

2) You can still do this without creating a field... but against which field are you actually testing this. For example, you might need a project where this true. So, in that case you can do this

Avg({$<Project_ID = {"=CREATED_DATE >= DATE"}>} CREATED_DATE - DATE)

But this is saying that a ProjectID will have a single CREATED_DATE and a single DATE.... if it has multiple of either of the fields... it won't work. So, the above set analysis will pick those projects where CREATED_DATE >= DATE. What is your field against which you can run this condition?

Re: Set analysis: compare two date fields

florian_kloster — Tue, 20 Nov 2018 13:38:51 GMT

Hey 🙂

first of all thank you very much! This is exactly what I am looking for. I can use the ID as this is an unique identifier. This results in what should be correct.

But concerning your thoughts about performance and flagging:
True, flagging does not increase the data amount, but I don't see the elegance in creating a flag for one single analysis if it is possible to do it without this work through.

Thank you very much and have a great day! 🙂
Florian

Re: Set analysis: compare two date fields

marcus_sommer — Tue, 20 Nov 2018 15:07:14 GMT

Did you try the second suggestion from Sunny with the rangemax() ?

- Marcus

Re: Set analysis: compare two date fields

marcus_sommer — Tue, 20 Nov 2018 15:15:25 GMT

Your flags need only to be 0 or 1 if you want to multiply anything with it. Otherwise by using them within a set analysis your flag could contain 1 for one metric and 2 for another and so on - depending on your data and requirements it might be possible to nest multiple and even overlapping flags into a single one. If it's worth to increase here the complexity against more simpler but redundant approaches ...

- Marcus