1 2 Previous Next 17 Replies Latest reply on Oct 19, 2016 11:52 AM by Luciano Vasconcelos

Need help to fill missing dates

Hi guys.

I have a daily basis that i need to show in continuous lines.

I' ve made lot of tries but i didn't get all correct values and presentation.

My values are the cumulative values, so i have to get the difference from previous day. Other requirements are about month break.

Values should be restarted  each month break and first day of each month should be his own value not the difference.

Twbx has not my tries becaus they were all wrong.

I've attached a twbx file with notes.

Thks.

• 1. Re: Need help to fill missing dates

Hi Luciano, is this what you're looking for? I showed missing values on Fecha and used this calculation, which computes along Fecha and restarts every month:

IFNULL(SUM(Value)-LOOKUP(SUM([Value]), -1), PREVIOUS_VALUE(0))

• 2. Re: Need help to fill missing dates

Er, actually, is this what you wanted? I just re-read your post. If this is what you expect instead, just change the calc to this:

IFNULL(SUM(Value)-LOOKUP(SUM([Value]), -1), 0)

• 3. Re: Need help to fill missing dates

Hi David.

In fact this way calcule correctly missing values but not solve the first day of month value.

Another preoccupation is that this formula will throw all lines to zero. It's not wrong but it would be better if lines were directly connected.

I'm trying something calculating first day of month with fixed in order to not use month as a break.

I'll tell you if it works soon.

• 4. Re: Need help to fill missing dates

David Li

Using fixed is correct since i don't show empty rows.

Here is my formula:

If ATTR( [FECHA] ) <= ATTR( [Fixed Min Fecha] ) then

ZN(SUM([Values]))

Else

ZN(SUM([Values])) - LOOKUP(ZN(SUM([Values])), -1)

End

[Fixed Min Fecha]

{Fixed [FECHA (Mês / Ano)], [LITERAL RAMO]:MIN( [FECHA] )}

• 5. Re: Need help to fill missing dates

Hi Guys,

I don't have a lot of experience with reporting on missing dates but this looked like a challenging problem so I thought I'd give it a swing.

Unfortunately I don't have a solution but I think I got pretty close to what you are looking for in a table format  but haven't been successful in translating it to a continuous chart.

It may be easiest to create a calendar table and join your data set to it.

Attached is what I have so far.  I'm going to take another look when I have some more free time.  Definitely an interesting problem.

• 6. Re: Need help to fill missing dates

No luck on the continuous graph by day with the current data but attached is an example using a calendar table which of course makes this view much easier to produce.  Not sure if it's an option for you but attached is twbx showing it in action. Check out the data with calendar and continuous with calendar tabs.

• 7. Re: Need help to fill missing dates

Like this?

here is my calculation:

• 8. Re: Need help to fill missing dates

It seems you entered "dates" into excel sheet as strings and my systems swapped your months with days. I will never understand why people do this while excel honors date as data type....

another version here:

• 9. Re: Need help to fill missing dates

Sorry Luciano - only now I looked through your other sheets and got it...

I think you are looking for one of these:

1. Missing dates not shown:

2. Missing dates shown:

• 10. Re: Need help to fill missing dates

My problem is that this is the simple version.

I'm reading an statistical file. In order to have a calendar i'd need a SAS program to build it every day.

• 11. Re: Need help to fill missing dates

Hi.

On 14-Sep value should be:

 Dia de Fecha Value 14 de setembro de 2016 56.073.181,11

14-Sep in this case is the first day in month.

Same logic applies to 3-Oct. This Value should be 16.272.714.

The other calculations are correct.

• 12. Re: Need help to fill missing dates

OK, one last version if you do not want to show missing dates:

• 13. Re: Need help to fill missing dates

Hi, this is almost correct. It's similar what a had gotten using fixed Min(date) to reset value on month break.

What i didn't get was to show zero when date is missing.

Any idea?

• 14. Re: Need help to fill missing dates

Yes:

where continuous value is:

2 of 2 people found this helpful
1 2 Previous Next