topic Re: metrics name and this year, last year, growth value in App Development

metrics name and this year, last year, growth value

Sat, 26 Mar 2016 23:42:32 GMT

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?

Any reply is welcome. Thanks!

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

Gysbert_Wassenaar — Sun, 27 Mar 2016 09:22:00 GMT

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

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

swuehl — Sun, 27 Mar 2016 11:37:10 GMT

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

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

Sun, 27 Mar 2016 21:57:03 GMT

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

Thank you!

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

Sun, 27 Mar 2016 22:31:09 GMT

Thank you !

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

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

Gysbert_Wassenaar — Mon, 28 Mar 2016 06:49:47 GMT

Source data?

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

Mon, 28 Mar 2016 07:00:45 GMT

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

Gysbert_Wassenaar — Mon, 28 Mar 2016 11:19:32 GMT

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

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

Mon, 28 Mar 2016 12:18:51 GMT

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

Gysbert_Wassenaar — Mon, 28 Mar 2016 17:01:36 GMT

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