Modify a field in a dimension in bar chart

[Anonymous]

Hi

I'm working with financial data and have a chart below. For each year, there are actual, budget and estimate revenue.
For the bar chart below, what's the beat way to replace the estimate revenue amount and change it to previous (2014)'s actual revenue?

Thanks

Zixiao

[MK_QSL]

Provide sample data for the same... We can create the same in script... It's difficult to calculated in Bar Chart in case if you want to show more than one year data at a time.

[Anonymous]

Here's the sample data

Product	Year	Month	Type	Amount
Product 1	2015	Jan	Bud	10,000
Product 1	2015	Jan	Est	9,000
Product 1	2015	Jan	Act	7,954
Product 2	2015	Jan	Bud	10,000
Product 2	2015	Jan	Est	7,000
Product 2	2015	Jan	Act	7,954
Product 1	2014	Jan	Bud	10,000
Product 1	2014	Jan	Est	8,000
Product 1	2014	Jan	Act	13,211
Product 2	2014	Jan	Bud	10,000
Product 2	2014	Jan	Est	6,000
Product 2	2014	Jan	Act	46,512

[MK_QSL · Accepted solution]

Use below script

T1:

LOAD

  *

Inline

[

  Product, Year, Month, Type, Amount

  Product 1, 2015, Jan, Bud, 10000

  Product 1, 2015, Jan, Est, 9000

  Product 1, 2015, Jan, Act, 7954

  Product 2, 2015, Jan, Bud, 10000

  Product 2, 2015, Jan, Est, 7000

  Product 2, 2015, Jan, Act, 7954

  Product 1, 2014, Jan, Bud, 10000

  Product 1, 2014, Jan, Est, 8000

  Product 1, 2014, Jan, Act, 13211

  Product 2, 2014, Jan, Bud, 10000

  Product 2, 2014, Jan, Est, 6000

  Product 2, 2014, Jan, Act, 46512

];

Map_Table:

Mapping Load 

  Product & Year & Month & Type as Key,

  Amount

Resident T1;

T2:

Load

  *,

  ApplyMap('Map_Table', Product & (Year-1) & Month & Type, 0) as PreYearAmount

Resident T1;

Drop Table T1;

Now create a bar chart

Dimension

Type

Product

Expression

IF(Type = 'Act', SUM({<Type = {'Act'}>}Amount),

IF(Type = 'Bud', SUM({<Type = {'Bud'}>}Amount),

SUM(PreYearAmount)))