4 Replies Latest reply on Dec 12, 2019 4:39 PM by shuhei saito

Coloured Current Month on Stacked Bar Not Working with Secondary Datasource

Vanessa Gray Dec 2, 2019 5:42 PM

Hello helpful people!

Attached workbook

I have a running sum over sales for a selected year. I want to highlight the values as they occur in the "current month" I have created a parameter to select the current month to make sure its working ...

Worksheet titled "Working" shows the sales in December on top of the YTD to Nov - and it looks great.

But when I add the targets, see worksheet titled "Not Working" it only colours the WHOLE month incl. the YTD figures for sales. see second image below

The targets have to be the primary datasource because the targets are set at the start of the year and I want the chart to show all months and they fill up as the year progresses.

Sales only occur in the sales table as the operational data comes in.

Can I colour the portion of the bar for a running sum within a secondary datasource

Workings attached

Help muchly appreciated

eg Sales Targets and Actuals Cumulative Bar.twbx 2.4 MB

1. Re: Coloured Current Month on Stacked Bar Not Working with Secondary Datasource

shuhei saito Dec 2, 2019 8:11 PM (in response to Vanessa Gray)
Hi Vanessa,

When you use Data Blending and row-level (non-aggregated) calculation from secondary data source, you need to add the granularity which identify each row in the view.
But in your case, it is not possible to add Order Date because primary data source does not have it.

So if you want to separate colors by today's month or not, I recommend you to separate aggregation and use Measure Names & Measure Values.

1. Create 2 calculated fields on secondary data source.

2. Add both field into the view with Measure Values, and set Running Total on both fields.

3. Add Measure Names to Color on Measure Values' axis.

(4. Change color allocation and title of color legend as you like.)

<result>

Please see attached workbook.

eg Sales Targets and Actuals Cumulative Bar_ss1.twbx 2.4 MB

1 of 1 people found this helpful
Like Show 1 Likes(1)

Actions
2. Re: Coloured Current Month on Stacked Bar Not Working with Secondary Datasource

Vanessa Gray Dec 9, 2019 1:14 PM (in response to shuhei saito)

Wow thanks so much!
Like Show 0 Likes(0)

Actions
3. Re: Coloured Current Month on Stacked Bar Not Working with Secondary Datasource

Vanessa Gray Dec 12, 2019 12:45 PM (in response to Vanessa Gray)

Hi Shuhei

Can we show the chart with all months for the year showing but only the data up to 'today' . So the future months will be blank (happy to colour white)

When I change the date to be today - it removes all future months - see image
Like Show 0 Likes(0)

Actions
4. Re: Coloured Current Month on Stacked Bar Not Working with Secondary Datasource

shuhei saito Dec 12, 2019 4:39 PM (in response to Vanessa Gray)
Hi Vanessa,

You mean Sales Target should always appear regardless of Parameter 'today'?
If so, please try below.

Remove this filter.

Create (or change) both true and false fields.

Replace to new fields into Measure Values.

<result - case when you select #8/15/2018# as today>

eg Sales Targets and Actuals Cumulative Bar_ss2.twbx 2.4 MB
Like Show 0 Likes(0)

Actions