Combo chart multiple date dimensions

[#1]

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?

Thank you for your time.

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.

[Clever_Anjos]

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

[jonbmorrison]

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?

[#4]

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.

[#5]

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

[Clever_Anjos]

Would you mind sharing a sample of your app?

[#7]

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.

[Clever_Anjos]

Please adapt your script and use Dates as your dimension

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

Cockpits:

LOAD

    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:

LOAD

    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]);

[#9]

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. 

[Clever_Anjos]

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)?