11 Replies Latest reply on Feb 10, 2016 12:24 PM by Shinichiro Murakami

# Multiple conditions using dates

I've been struggling with this one... The goal is to label Categories that contain all 3 Sub-Categories (a, b, c) that happen within a 5 day range 1 else 0. My approach has been to find the max of all min dates for sub-cat, then the min of all max dates for sub-cat, and compute the datediff within the category level. Is there a better way to go about this problem?

• ###### 1. Re: Multiple conditions using dates

Couple of questions.

How you define 5 days range?

is this universal across Category or category specific?

And if there are more than 5 days, how you determine start/end?

Thanks,

Shin

• ###### 2. Re: Multiple conditions using dates

Defining where the 5 day range begins/ends is part of the challenge. The actual data I'm not able to share, however I can tell you it is log data so there are many dates for each subcategory. Just to rephrase the 5 day requirement... Mark 1 if the Category contains all 3 Subcategories AND the 3 types of Subcategories must occur within a 5 day range. However, if the Category contains all Subcategories (a,b, c) but at least one of the Subcategory occurs outside that mysterious 5-day range, then Mark is 0. The Mark field is basically a binary Y/N for the Category level that I'm trying to create.

• ###### 3. Re: Multiple conditions using dates

Xiao,

I'm not sure this workbook works or not because I don't know how you use this data after this,

Anyways, putting window calculation and created some flag.

[window_count (abc)]

window_count(count(if [Sub Categpry]="a" then [Row ID] end ),-4,0)*

window_count(count(if [Sub Categpry]="b" then [Row ID] end ),-4,0)*

window_count(count(if [Sub Categpry]="c" then [Row ID] end ),-4,0)

[Y/N (1,0)]

iif([window_count (abc)]>0, 1,0)

Thanks,

Shin

9.0 attached

• ###### 4. Re: Multiple conditions using dates

Found something wrong.

Working.

Shin

• ###### 5. Re: Multiple conditions using dates

re-sending.

Shin

• ###### 6. Re: Multiple conditions using dates

Should've included my end goal. It's to build a histogram or line chart showing number of "Marks" over time. The actual data drills down to the minute and second so that would show a trend by days.

• ###### 7. Re: Multiple conditions using dates

It's a little bit trouble some as my first impression..

Is the day range "5" is pretty stable in this case?

If this becomes 14 or so, approach should be very different.

Thanks,

Shin

• ###### 8. Re: Multiple conditions using dates

Can you explain how changing the day range would lead to a different approach? Wouldn't you just have to adjust the window sum offset from -4 to x?

edit: Also would you have to create a new calculation for every category/subcategory present? I may have oversimplified the example. I really appreciate your help on this whole thing though! You've provided a new perspective to this prob.

• ###### 9. Re: Multiple conditions using dates

Window_sum always requires table itself as very stable/restricted format .

Now I read your request again and understand the approach I though does not work.

So, "5" // or something // continuous time grain is important for you and both "5" and "grain"  should be flexible. that's your request, correct?

Shin

• ###### 10. Re: Multiple conditions using dates

The 5-day range is really just an example to simplify the problem. The range wouldn't need to be dynamic, rather defined. Sorry if I'm making this confusing...

• ###### 11. Re: Multiple conditions using dates

K,

Let's change the approach completely.  But I don't know you like this or not.  Anyway, it's still fastest way to me.

Approach's name is "Create datasource from worksheet"

Create below table with window calc.  But I added parameter for you to play with.

BTW, my sample data sometimes missing date, but let's assume it's filled.

On 2nd Data source,

Two "Measure Names" are quite confusing,  but not a vital issue.

[a x b x c]

min(1,({fixed [Category],[Date]:sum(if [Sub Categpry]='a' and [Measure Values] >=1 then 1 else 0 end )}*

{fixed [Category],[Date]:sum(if [Sub Categpry]='b' and [Measure Values] >=1 then 1 else 0 end)}*

{fixed [Category],[Date]:sum(if [Sub Categpry]='c' and [Measure Values] >=1 then 1 else 0 end )}))

Now on new worksheet, you can handle the data easily......

Thanks,

Shin