1 2 Previous Next 22 Replies Latest reply on Aug 4, 2017 2:10 AM by kettan

# Calculation depending on a Start Date and a End Date and represent it on a time axis

Hello

I have a table with a key NUM_CONTRAT with a Start Date and a End Date.

I want to create a measure which calculate the Sum of the CONTRAT OPEN by period and represent if on a chart with a time axis.

I have tried with a parameter but the problem is thaht i cannot fix it in lines or columns

It appears easy but i have no idea to do that

thanks for help,

Ed

• ###### 1. Re: Calculation depending on a Start Date and a End Date and represent it on a time axis

It will be good if you either provide your packaged workbook or the data for someone to provide a solution.

• ###### 2. Re: Calculation depending on a Start Date and a End Date and represent it on a time axis

1. Pivot Your Data (Start date and End Date)

2. IF Pivot field name is Start Date Then 1 Else -1

3. Sum your Pivot field names. Get a count. That would be count of currently Open and using Pivot field values divide them into months on X axis. Something like that.

Try

Thanks

Deepak

• ###### 3. Re: Calculation depending on a Start Date and a End Date and represent it on a time axis

As long as it doesn't generate too many rows, my preference would be to  Split Periods Into Rows Dynamically,  that is, one row for each reporting period.  Otherwise, I would split it into two as already suggested by Deepak Rai.  This latter method is probably the one preferred by most.  Each method has its strengths and weaknesses.

PS. This is a very common challenge as documented in  FAQ:  Open & Close Dates

• ###### 4. Re: Calculation depending on a Start Date and a End Date and represent it on a time axis

Hello Thanks for response,

I have join you the TWBX and excel file with data.

In the excel fil there is an exemple of the chart I want.

The value in the chart in the time axis time are valorized only if the contrat is open during this period.

Thankd,

Edouard

• ###### 5. Re: Calculation depending on a Start Date and a End Date and represent it on a time axis

As per your data, I created the view. However, It looks that whatever contract you started in 2008 and 2009 are already closed all in 2014. Check Screenshots and Sheets 4 and 6 in attached. I pivoted your Start and End Date and wrote calculations.

Hope it Helps!!!

Let me know

Thanks

Deepak

• ###### 6. Re: Calculation depending on a Start Date and a End Date and represent it on a time axis

Hello Deepak,

Thanks for feedback.

I get your point but it s not totally what I want.

-> Regarding the screenshot 1, in the chart there is "-2" value.

-> Whereas, i have in the table 2 Contrats opened in 2014, and i want to display "2" instead of "-2"

Thanks,

Ed,

• ###### 7. Re: Calculation depending on a Start Date and a End Date and represent it on a time axis

Hello Kettan,

Thanks for feedback, i have to test on my side in give you a feedback.

This kind of solution create a cartesien product.

it cannon be a problem in term of performance ?

Thanks,

Ed

• ###### 8. Re: Calculation depending on a Start Date and a End Date and represent it on a time axis

in the same way,

there are only 3 Contrat Open in 2009 :

Where as i have in the table all my contract open in 2009 :

• ###### 9. Re: Calculation depending on a Start Date and a End Date and represent it on a time axis

Look at Screenshot and Sheet 8 at attached.

Thanks

Deepak

• ###### 10. Re: Calculation depending on a Start Date and a End Date and represent it on a time axis

Does the attached return the expected output?

Source Data (table for splitting data period into reporting periods)

Join (dummy cross join, the real join is the filter)

Filter As "Join"

Filter (Year)

[Period] <= DATEDIFF('year',[Start Date],[End Date])

Other Calculations

The below goes beyond your question, but added because it might be useful to have.

Period Year Start

Period Year End

Days In Period

DATEDIFF('day',

MAX( [Start Date], [Period Year Start] ),

MIN( [End Date], [Period Year End] )

) + 1

Days Full

DATEDIFF( 'day', [Start Date], [End Date] ) + 1

View Data  (for understanding the splitting of periods)

Chart

2 of 2 people found this helpful
• ###### 11. Re: Calculation depending on a Start Date and a End Date and represent it on a time axis

This kind of solution create a cartesien product.

No, it doesn't create a Cartesian product. The FILTER acts as the JOIN.

I don't know if it would be more efficient, but it would be nice if the "join" filter could be filtered with the join dialog as requested in  Support non-equi-join criteria for new Excel & text connector>.  I don't know for sure, but think we can use non-equi-join criteria when connected to a supported SQL database (SQL Server, Oracle, MySQL etc).

it cannon be a problem in term of performance ?

Yes, it can become a performance issue, but not necessarily so.

With your sample data, 20 rows became 73 "contract years" in total.

And even 14 fewer with filter set to Reporting Year >= 2009.

 Data Rows >= 2009 Raw : 20 20 Split into Two (union) : 40 40 Split into one row for each year of contract period : 73 59

How big is your data? How many contracts does it how?

And how many "contract years" do they add up to?

What system do you query (Excel, database, etc)?

Ps.  See my answer here:  Re: Calculation depending on a Start Date and a End Date and represent it on a time axis

1 of 1 people found this helpful
• ###### 12. Re: Calculation depending on a Start Date and a End Date and represent it on a time axis

Hi,

Thanks for feedback.

I have tested on my side and it is working well !

(I didn't open your twbx because of it was not possible with my version, i have rebuilt it).

Do you think it is possible to to the same with the possibility to zoom in the year.

I mean with a common time hierarchy (YEAR, QUARTER, MONTH, DAY).

Thanks,

• ###### 13. Re: Calculation depending on a Start Date and a End Date and represent it on a time axis

Hi kettan

I have around 250 000 lines in my table

Edouard,

• ###### 14. Re: Calculation depending on a Start Date and a End Date and represent it on a time axis

I use a bigquerry connection.

It is the first time i use it but it seems to be powerfull.

Edoaurd,

1 2 Previous Next