Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Contributor II
Contributor II

Straight table with values per year, delta values

Hi there,

I am having trouble creating a table showing the following:

20142015ΔΔ [%]
Turnover1001505050%
Tons7515075100%
................

Right now I'm using a horizontal straight table to show the first three columns which works fine. I am having trouble however adding the delta values to the table because they aren't dimensions but set analysis expressions. I also tried using a second table next to this one w/o luck.

I also thought about pre-calculating all the different delta values in the load script but that's way too many dimensions I'd have to consider.

Has anyone ever done or come across this? I'm not sure this will work at all because it's mixing up dimensions and expressions.

Thanks,

-Stefan

1 Solution

Accepted Solutions
Highlighted
MVP
MVP

First of all, the example you have provided is different then the question you have asked.

Kindly use below script..

tblExample:

LOAD * INLINE [

  Year, Turnover, Tons

  2014, 100, 75

  2015, 150, 150

];

Now use below in Straight or Pivot Table

Dimension

Use Calculated Dimension

=ValueList('TurnOver','Tons')

Expressions

1)

Label

=Year(Today())-1

Definition

=

IF(ValueList('TurnOver','Tons') = 'TurnOver', SUM({<Year = {'$(=Year(Today())-1)'}>}Turnover),

IF(ValueList('TurnOver','Tons') = 'Tons', SUM({<Year = {'$(=Year(Today())-1)'}>}Tons)))

2)

Label

=Year(Today())

Definition

=

IF(ValueList('TurnOver','Tons') = 'TurnOver', SUM({<Year = {'$(=Year(Today()))'}>}Turnover),

IF(ValueList('TurnOver','Tons') = 'Tons', SUM({<Year = {'$(=Year(Today()))'}>}Tons)))

3)

Label

Diff

Definition

Column(2)-Column(1)

4)

Label

Diff%

Definition

(Column(2)-Column(1))/Column(1)

Go to Number Tab

and Select last Expression Diff%

Fixed to 2 Decimals

Tick Show in Percent (%)

View solution in original post

2 Replies
Highlighted
MVP
MVP

First of all, the example you have provided is different then the question you have asked.

Kindly use below script..

tblExample:

LOAD * INLINE [

  Year, Turnover, Tons

  2014, 100, 75

  2015, 150, 150

];

Now use below in Straight or Pivot Table

Dimension

Use Calculated Dimension

=ValueList('TurnOver','Tons')

Expressions

1)

Label

=Year(Today())-1

Definition

=

IF(ValueList('TurnOver','Tons') = 'TurnOver', SUM({<Year = {'$(=Year(Today())-1)'}>}Turnover),

IF(ValueList('TurnOver','Tons') = 'Tons', SUM({<Year = {'$(=Year(Today())-1)'}>}Tons)))

2)

Label

=Year(Today())

Definition

=

IF(ValueList('TurnOver','Tons') = 'TurnOver', SUM({<Year = {'$(=Year(Today()))'}>}Turnover),

IF(ValueList('TurnOver','Tons') = 'Tons', SUM({<Year = {'$(=Year(Today()))'}>}Tons)))

3)

Label

Diff

Definition

Column(2)-Column(1)

4)

Label

Diff%

Definition

(Column(2)-Column(1))/Column(1)

Go to Number Tab

and Select last Expression Diff%

Fixed to 2 Decimals

Tick Show in Percent (%)

View solution in original post

Highlighted
Contributor II
Contributor II

Thank you very much! Exactly what I was looking for.