topic Re: Combo chart multiple dimensions in QlikView

Combo chart multiple date dimensions

Mon, 12 Dec 2016 15:26:02 GMT

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.

Re: Combo chart multiple dimensions

Clever_Anjos — Mon, 12 Dec 2016 15:40:16 GMT

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

Jon_M — Mon, 12 Dec 2016 16:23:32 GMT

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

Tue, 13 Dec 2016 13:04:28 GMT

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

Tue, 13 Dec 2016 13:07:27 GMT

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

Clever_Anjos — Tue, 13 Dec 2016 13:55:03 GMT

Would you mind sharing a sample of your app?

Re: Combo chart multiple dimensions

Tue, 13 Dec 2016 15:08:09 GMT

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

Clever_Anjos — Tue, 13 Dec 2016 15:23:42 GMT

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

Re: Combo chart multiple dimensions

Tue, 13 Dec 2016 15:37:35 GMT

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

Clever_Anjos — Tue, 13 Dec 2016 15:54:59 GMT

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

Wed, 14 Dec 2016 12:00:52 GMT

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

sunny_talwar — Wed, 14 Dec 2016 15:11:48 GMT

Hi sebhesjuan‌

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

Wed, 14 Dec 2016 15:30:10 GMT

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

sunny_talwar — Wed, 14 Dec 2016 15:42:46 GMT

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

Wed, 14 Dec 2016 15:55:07 GMT

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

sunny_talwar — Wed, 14 Dec 2016 16:02:46 GMT

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

Wed, 14 Dec 2016 16:07:34 GMT

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

sunny_talwar — Wed, 14 Dec 2016 16:28:48 GMT

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

sunny_talwar — Wed, 14 Dec 2016 16:36:46 GMT

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

Wed, 14 Dec 2016 16:42:29 GMT

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