1 2 Previous Next 15 Replies Latest reply on Jan 16, 2018 2:59 PM by Shinichiro Murakami

# Creating Consecutive Totals with Missing Dates

Hello,

I have a bit of an issue with a data set and was looking for some help.

I have a data set that involves counting the consecutive days an ID occurs so i can flag the amount when it surpasses a limit.

My Data will have a date for every ID. However, not every ID has a date. So in Some cases i have the following:

I've tried to do a running Sum with several iterations of If statements but have had no luck,

I have even tried a fixed LOD to count the ID's daily such as

{ FIXED [Asset ID],[Reference Day]:COUNTD([Asset ID])}

And have then tried to use an if statement to count them and reset if the value was 0 but was unsuccessful.

I understand that in some cases the date is not present in the table such at day 15 here.

So what i want to do is store the counts up to the "missing day" as technically this "should be" 0 and then create a new total for the next set of consecutive days. I want to store each consecutive batch of totals per AssetID and then i can evaluate by ID if it has exceeded a certain limit, lets say 5 consecutive days in this case, at any point in time.

Is there an easy way to do this without trying to pad the data for the missing dates. If not, and assuming i did pad it, what would be the approach? I've attached a copy of the workbook

Thanks - appreciate any help!

• ###### 1. Re: Creating Consecutive Totals with Missing Dates

HI Matthew

Here is my appraoch.

Use "Show missing value" and "previoius_value"

Thanks,

Shin

• ###### 2. Re: Creating Consecutive Totals with Missing Dates

Hi Shin

You have a measure in your display of intervalconsecutive5. What is that exactly as there is no snapshot..

• ###### 3. Re: Creating Consecutive Totals with Missing Dates

I don't understand your question quite well. Rather, I suspect that you did not see my attachment?

You cannot see the attachment from inbox view, please go to original post and find it.

Thanks,

Shin

• ###### 4. Re: Creating Consecutive Totals with Missing Dates

Ah i see lol.

Ok ya i was able to download the workbook. I wasn't sure what the intervalconsecutive5 was doing. It was just an extra column in my data source.

Can you explain to me what the isnull is specifically doing?

if first()=0 then 1

elseif isnull(min([intervalconsecutive5])) then 0 else previous_value(0)+1

end

• ###### 5. Re: Creating Consecutive Totals with Missing Dates

Hi Matthew,

See the help to understand the logic formula.

Then, could you mark my answer as correct / helpful to close the thread, not from inbox view, but from original post.

.

Regards,

Shin

• ###### 6. Re: Creating Consecutive Totals with Missing Dates

hey shin,

I think we are almost there:

If you look at my original post :

"I want to store each consecutive batch of totals per AssetID and then i can evaluate by ID if it has exceeded a certain limit"

So In the data you hae posted we are marking red the data over the 5 day mark.

What i am trying to do is create two bar graphs. 1 that will show on the Y-axes number of ID, and on the X-axes the number of consecutive working days. The second will show by ID the number of times it goes over 5 consecutive working days. ( note if a personoes 7 days it will count as 1 , if they go over 10 days it will be 2, and so on.)

• ###### 7. Re: Creating Consecutive Totals with Missing Dates

Hi Mathew,

Other viewers are also referring this thread, and could you please either of "my" answer as correct to show the appropriate part of correct answer.

Filter date by index. you need to keep date dimension for table calculation stay working.

Thanks,

Shin

• ###### 8. Re: Creating Consecutive Totals with Missing Dates

Hi Shin,

Is there an easy way to swap this to show the day on the x -axis?

• ###### 9. Re: Creating Consecutive Totals with Missing Dates

HI Matthew

I don't understand the logic to put "day" as axis.

How do you calculate measure in that case?

or do you mean put ID on Y-axis?

Shin

• ###### 10. Re: Creating Consecutive Totals with Missing Dates

I would like to see the total consecutive day exceedences ( i.e in the multiple of 5s) on the y axis and the day on the x-axis.

So that would mean summing up the excedences of all the individual IDs for a given day to show the total each day.

Sent from my Samsung Galaxy smartphone.

• ###### 11. Re: Creating Consecutive Totals with Missing Dates

See attached.

Shin

• ###### 12. Re: Creating Consecutive Totals with Missing Dates

Thanks Shin, This is pretty much what I need. I switched it to a bar graph here. The only issue i have is when i hover over the columns it shows one of the Asset ID's even though its totaling all of them. Is there a way to remove it?

• ###### 13. Re: Creating Consecutive Totals with Missing Dates

Hi Mathew,

Here you go.

Shin

1 of 1 people found this helpful
• ###### 14. Re: Creating Consecutive Totals with Missing Dates

You are the Best!

1 2 Previous Next