# Qlik Sense App Development

Not applicable

## metrics name and this year, last year, growth value

Hello everyone:

I have some metrics, such as metric name 1, metric name2. Now I can use pivot table to display metrics name as first column,

and dimension year as follow colums.

The new  pivot table that I want, the first column is metric names, and the second column is this year value, the third column is previous year  value, and  so on. How can I do it in qlik sense desktop?

Tags (1)
9 Replies

## Re: metrics name and this year, last year, growth value

I'm not sure what you're asking. Can you post an example in Excel?

MVP

## Re: metrics name and this year, last year, growth value

If I understood correctly, you have your expressions (your metrics / aggregations) pivoted to the left and the year dimension pivoted to the top, right?

You can use calculated dimension or set analysis to limit the year dimension, like (for a calculated dimension)

=If( YearField <= Year( Today() ), YearField)

Then sort your YearField dimension descending and don't show NULL values.

If you want to compare this year's metric with last year's metric, you can use chart inter record functions, like Before() / After() in a pivot table with pivoted dimension (or Above() / Below() with unpivoted dimensions), for example to calculate growth rate for aggregated Metric1 field:

=Sum(Metric1) / After( Sum(Metric1) ) -1

Hope this helps,

Stefan

Not applicable

## Re: metrics name and this year, last year, growth value

When I select some year(only one), my goal is the picture below.

Thank you!

Not applicable

## Re: metrics name and this year, last year, growth value

Thank you !

Because the metric is dynamic, so Sum(Metric1)  is not accessable that I think.

Source data?

Not applicable

## Re: metrics name and this year, last year, growth value

Use MetricName as dimension and these three expressions as measures:

• This year: sum({<Year={\$(=max(Year))}>}Amount)
• Previous year: sum({<Year={\$(=max(Year)-1)}>}Amount)
• Growth: num(sum({<Year={\$(=max(Year))}>}Amount)/sum({<Year={\$(=max(Year)-1)}>}Amount)-1,'#,##0.0%')
Not applicable

## Re: metrics name and this year, last year, growth value

Sorry, I mislead you. The metric is some set analysis expression, such as sum({\$<item=cost>} amount). The source data is as follows:

CostMetric = sum({<ItemName={'Cost'}, subItemType={"*"}>}, Amount)

PayinMetric = sum({<ItemName={'Payin'},subItemType={"*"}>},Amount)

BedsMetric = sum({<ItemName={'Beds'}>},Amount)

## Re: metrics name and this year, last year, growth value

Then use a synthetic dimension and use pick-match combinations for each of the three measures. You can find a simple example of such an expression here: Re: How to make expressions look like dimension???