Set analysis: compare two date fields

[florian_kloster]

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

[sunny_talwar]

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))

[florian_kloster]

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

[sunny_talwar]

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))

[florian_kloster]

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

[sunny_talwar]

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?

[florian_kloster]

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

[marcus_sommer]

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

- Marcus

[marcus_sommer]

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