23 Replies Latest reply: Dec 15, 2016 10:06 AM by Sebastian Heselaars

# Combo chart multiple date dimensions

Hi,

Does anyone know a simple solution to fit two different line measures with different date dimensions from each other into one graph? I am trying to visualize the cumulative returns (daily for midweek data) with the corresponding Model score (+/- weekly) for a stock.

I thought a simple combo chart will do the work, but because both measures use different dimensions (frequent vs less frequent dates) only either one will be shown correctly. My solution is to reload the less frequent measure (Model score) for the dates for which no model score is available. In this way there would be the same amount of data available for both measures and hopefully this will result in the graph I am looking for.

As this is a brand new territory for me, could someone please help me creating this script if this indeed seems the correct solution?

Sebastian

Summary: Combo chart only supports one dimension for multiple measures. How to fit two different line measures with two different date dimensions into one graph? Dimensions are called "Dates" (daily for midweek data) for returns and "DatesCockpit" (+/- weekly) for Model scores.

• ###### Re: Combo chart multiple dimensions

Maybe if you concatenate the fields of your 4 different dimensions, creating 2 dimensions with the values you need and change your expressions you can do what you need

• ###### Re: Combo chart multiple dimensions

Hi Anjos,

Thank you for your quick reply. Perhaps I should have been more clear, currently I  am already dealing with two dimensions. These dimensions are both dates. However, one set of dates is daily (the ones of the measure returns), while the other set of dates is +/- weekly (the ones of the measure Model score).

When I try to visualize both lines in a combo graph, only the line of the Model score line will be shown correctly. The returns are now not shown on a daily, but weekly basis.

Please have a look at the figures below:

The lower figure represents the Returns on a daily basis. The upper figure represents the Model score & Returns, but uses the dates of the Model score which is on a weekly basis. Therefore, misrepresenting the returns series. I would like to find a solution for the latter if possible.

• ###### Re: Combo chart multiple dimensions

Would you mind sharing a sample of your app?

• ###### Re: Combo chart multiple dimensions

Please find a sample app attached. It's the first time I attach a qvf file. I hope everything went well and you are able to open the app.

• ###### Re: Combo chart multiple dimensions

// Load Cockpit files with old column names (Newbet = WeightManualInput, Target = New Weight)

Cockpits:

Date(Date#(LEFT(right(Filename(),13),8),'YYYYMMDD'),'DD-MM-YYYY') AS DatesCockpit,

Date(Date#(LEFT(right(Filename(),13),8),'YYYYMMDD'),'DD-MM-YYYY') AS Dates,

Sedol,

Reuters as "Ticker",

Name,

Model

FROM [lib://Mutation File/Renamed Cockpit List Full\Old Format\Cockpit *.xlsm]

(ooxml, embedded labels, header is 1 lines, table is [Stock Selection]);

// Load Cockpit files with new column names (Newbet = WeightManualInput, Target = New Weight)

Cockpits:

Date(Date#(LEFT(right(Filename(),13),8),'YYYYMMDD'),'DD-MM-YYYY') AS DatesCockpit,

Date(Date#(LEFT(right(Filename(),13),8),'YYYYMMDD'),'DD-MM-YYYY') AS Dates,

Sedol,

Reuters as "Ticker",

Name,

Model

FROM [lib://Mutation File/Renamed Cockpit List Full\New Format\Cockpit *.xlsm]

(ooxml, embedded labels, header is 1 lines, table is [Stock Selection]);

• ###### Re: Combo chart multiple dimensions

Thank you for your quick responds Anjos. I have implemented your solution and it is certainly helpful. However, the return series is now shown on a weekly basis and not on a daily basis.

Could it be a solution to rewrite the code in the Data load editor such that we could equal the frequence of the Model score to the return series? That is, use the same Model score for each day for which no new Model score is available, until a new Model score is met. In this way we would equal the Return- and Model score frequency, namely on a daily basis. I have not much experience in writing these codes, but I could spend some time trying if this seems appropriate.

• ###### Re: Combo chart multiple dimensions

I´m not sure about what you have there

DatesCockipt are week based and Dates are daily based, right?

Would you mind attaching a screenshot of your new model (using my code)?

• ###### Re: Combo chart multiple dimensions

Anjos, excuse me for my late responds. You are right on the frequency of DatesCockpit and Dates. Find bellow the new pictures using your code:

For some reason the frequency of Dates also became also weekly, instead of daily (see the second chart). The model score in the above figure is correct, the returns in the top figure are correct on a weekly basis, but I'd like to have them on a daily basis. As you can see in my earlier comment the daily returns (lowest figure) give a different outcome than the weekly returns.

• ###### Re: Combo chart multiple dimensions

Since I am a latecomer to this thread and I have too much time to go over what has already happened, I would ask you to very briefly explain what is needed and may be the exact numbers you are looking to get? An Excel file which shows the numbers for the two lines would be great.

Best,

Sunny

• ###### Re: Combo chart multiple dimensions

Hi Sunny T,

In short, I need a combo chart representing two different series (Returns and Model Scores). The problem is that both series have a different frequency of dates for which numbers are available (+/- daily vs +/- weekly respectively). I have added two excel files containing the required numbers. In the "Combo Chart with correct Model Scores"-file the cumulative return is not represented well due to the difference in date frequency. Please let me know if anything remains unclear after this explanation.

With kind regards,

Sebastian

• ###### Re: Combo chart multiple dimensions

I understand the required output, now the question is what would you want to see in your combo chart? Daily Data or Weekly Data?

• If Daily, then Model will remain same for all dates?
• If Weekly, then how will returns show up at week level? Sum, Avg or some other aggregation of each day within the week?
• ###### Re: Combo chart multiple dimensions

In my combo chart I would like to see the same frequency as used for the returns (+/- daily).

However, the model score is on a weekly basis, which causes the problem. I originally thought if the model score could somehow be copied to the same frequency of dates as the return data, that this might provide the correct solution.

• ###### Re: Combo chart multiple dimensions

That is what I am trying to understand. Right now you have this

 DatesCockpit (Model) 14-05-2014 123% 19-05-2014 138%

This would show on a daily chart like this?

 DatesCockpit (Model) 14-05-2014 123% 15-05-2014 123% 16-05-2014 123% 17-05-2014 123% 18-05-2014 123% 19-05-2014 138%

or something else?

• ###### Re: Combo chart multiple dimensions

Yes, exactly. Please note that the dates of "Dates" is not exactly on a daily basis. Weekend are often omitted.

• ###### Re: Combo chart multiple dimensions

I am looking at your first attachement (2nd one only have weekly data for returns, so had to go to 1st attachment). Within that I am unable to get 123% for 14-05-2014? How are you getting this? Its not the Avg() or Sum() of all rows, right? What kind of aggregation do I need to get this 123%?

• ###### Re: Combo chart multiple dimensions

Hi Sunny,

I should have stated that the Model score is not related to the return of the stock whatsoever. These are fixed numbers computed based on much different data which are not related to the returns at all.

Optimally, the combo chart should therefore give an impression to what extent the Model score indeed predicts the returns in the future.

Excuse me for the inconvenience.

Regards,

Sebhastian

• ###### Re: Combo chart multiple dimensions

Can you check my response below and see if that is what you are looking to get?

• ###### Re: Combo chart multiple dimensions

Just trying to ensure I'm following your question

You have mixed granularity on your dates?   Daily for one data set and Weekly for the other?

As Clever suggested, perhaps you can join data sets on the dates - see threads on Mixed Granularity  - perhaps start here Fact Table with Mixed Granularity

then your X axis would be common dates and you would have two distinct Y axis values?

• ###### Re: Combo chart multiple dimensions

Hello Jon,

Thank you for your reply as well. I am going to have a more closer look on the link you provided. Please read my answer above to make sure my issue is clear to you. From you answer, I think you well understood my problem.

With kind regards,

Sebastian

• ###### Re: Combo chart multiple dimensions

Try this for model with Dates as dimension

Aggr(If(Floor(Dates) >= Floor(WeekStart(DatesCockpit)) and Floor(Dates) <= Floor(WeekEnd(DatesCockpit)), Model), Dates, DatesCockpit)

In table, it would look like this:

HTH

Best,

Sunny

• ###### Re: Combo chart multiple dimensions

Sunny,

This looks indeed very much like the appropriate solution! To be 100% sure, I am going to verify this tomorrow. I am really grateful that you could make some time free to help me out. I'll come back to you tomorrow morning!

Best,

Sebastian

• ###### Re: Combo chart multiple dimensions

Sounds like a plan. Just know that this is going to be very slow because Aggr() function is creating a Cartesian product between the two tables to get the desired output. May be it might make sense to do an interval match in the script to improve performance.

Best,

Sunny

• ###### Re: Combo chart multiple date dimensions

Good day Sunny,

Your suggested solution was indeed the one I was looking for! I have slightly adjusted the original question and title to have a better understanding for other people who might face the same problem.

Performance became indeed much slower. I'm going to have a look into your interval match suggestion to see how I can improve speed. Thanks you once again!

With kind regards,

Sebastian