topic Re: Modify a field in a dimension in bar chart in QlikView

Modify a field in a dimension in bar chart

Anonymous — Tue, 10 Mar 2015 15:52:25 GMT

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

Re: Modify a field in a dimension in bar chart

MK_QSL — Tue, 10 Mar 2015 15:59:28 GMT

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.

Re: Modify a field in a dimension in bar chart

Anonymous — Tue, 10 Mar 2015 16:02:12 GMT

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

Re: Modify a field in a dimension in bar chart

MK_QSL — Tue, 10 Mar 2015 16:37:23 GMT

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