Pointing out parts of time series in a graph?

[marko_rakar]

I would like to create a graph of sales within a period which will have (somehow) pointed out periods when we had some parallel activity.

For example:

[Sales]:

Load * INLINE

[

    ID, Name, Total_amount, Date

    1,   Zeus, 1000, 01.01.2014

    2,   Zeus, 1500, 01.02.2014

    3,   Zeus, 2700,  01.03.2014

    4,   Zeus, 2200,  01.04.2014

     ];

[Marketing activities]:

Load * INLINE

[

    Name, Value, Date1

    Zeus,   0, 01.01.2014

    Zeus,   250, 01.02.2014

    Zeus,   350, 01.03.2014

    Zeus,   0, 01.04.2014

     ];

Now, in this case, I would like to have a line graph which will show volume of sales in four months, and based on the marketing activities recorded in second table, I would like to have part of the line where sales and marketing overlap somehow marked (different kind of line, different color, shaded area below the line or something). In this particular case I would need to have the line "marked" for february and march.

Also, the purpose of "value" in the marketing activities table is to add some calculation on how much money was spent vs. (expected) sales increase in the period (so that from delta calculated from expected and actual sales we derive how much more sales was achieved vs. cost of the marketing campaign).

Any ideas or examples on how to achieve this?

(or ideas on how to show this in any other way)

[Gysbert_Wassenaar]

See attached qvw

[bertdijks]

Steps:

1. Rename the date1 to date

2 use the concatenate function (the solution of Gysbert creates a synchronisation issue, although it works, you can also use a new key for example Name&Date as Key)

example:

Load * INLINE

[    ID, Name, Total_amount, Date
    1,   Zeus, 1000, 01.01.2014
   2,   Zeus, 1500, 01.02.2014
   3,   Zeus, 2700,  01.03.2014
   4,   Zeus, 2200,  01.04.2014
    ];

Concatenate
[Marketing activities]:
Load * INLINE
[   Name, Value, Date
    Zeus,   0, 01.01.2014
    Zeus,   250, 01.02.2014
    Zeus,   350, 01.03.2014
   Zeus,   0, 01.04.2014
     ];

Finnaly you can change the color of the bar with a formula

You can also add a line for delta or something like that.

Kind regards,

Bert

[Gysbert_Wassenaar]

2 use the concatenate function (the solution of Gysbert creates a synchronisation issue, although it works, you can also use a new key for example Name&Date as Key)

What synchronisation issue would that be? What should get synchronised and isn't (correctly) ?

[marko_rakar]

concatenate is not an option simply because I might have my competition marketing or sales information as well and there is simply nothing to concatenate to

also, if we have many different products or possibly many marketing channels we also need to be able to select those as well so you might have more then one value for each product