Difference between two dates with a few rules

gitguto — Thu, 27 Jan 2022 15:27:23 GMT

Hello everyone,

First here's a table to better understand what my data looks like:

I need to create a measure that calculates the difference between the finish date of the activity Study approval and the Start Date of the activity Development Stage for each Request Number. And then, group them in bins and count how many requests took 0~15 days, 16~30 days and 30+days.

But I need to filter out a few things:
1) count only request numbers that have a finish date for both activities. (some request numbers dont have both activities, and some have them both but dont have the finish date for 'study approval')
2) count only request numbers that do NOT have "aux" = 2.

I manage to create the grouped dimensions with this formula, but I can't find a way to filter out these two rules.

sum(aggr(if( only({<name = {'Study approval'}>}activityfinishdate)
-only({<name = {'Development stage'}>}activitystartdate)<16,1,0),[Request Number]))

Here's the chart I'm trying to build:

Thank you very much!

Re: Difference between two dates with a few rules

chris_djih — Fri, 28 Jan 2022 08:39:44 GMT

the 2nd condition is quite easy, just add this to your set-analysis: aux -= {2}.
the first condition i would shift into the script:

Newtable: Load RequestNumber, If(activityCount+finsihDateCount=4,1,0) as condition_flag Load RequestNumber, count(activity) as activityCount, count(FinshDate) as finsihDateCount resident yourtable group by RequestNumber;

You can then add this to your Set-Analysis: condition_flag={1}

topic Re: Difference between two dates with a few rules in App Development

Difference between two dates with a few rules

Re: Difference between two dates with a few rules