Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Qlik® Product Spotlight: Discover what’s possible. Get more from our products.
See for yourself. Register today.
jelindbe
New Contributor II

Year plus one

I need to display a measure for "2010" as "2011", "2011" as "2012"....etc.

I have tried this formula (with many variations):

Sum({<Year={$(={Year}+1)} >}my_measure)

but it returns with 0. I would greatly appreciate any assistance.

Tags (1)
1 Solution

Accepted Solutions
OmarBenSalem
Esteemed Contributor

Re: Year plus one

To adapt to ur case Jon Erik:

use a chart, as dimension: Year

as a measure:

sum({<Year>}aggr(below(sum({<Year>}YourMeasure)) , (Year,(NUMERIC, ASCENDING))))

13 Replies
OmarBenSalem
Esteemed Contributor

Re: Year plus one

put as a dimension ur year; and as a measure:

below(sum(my_measure))*avg({<Year>}1)

consultant_bi
Contributor

Re: Year plus one

because you are using an aggregation to display the sum of sales or something of a year, i thought about a simple solution you can play just on the visualisation add one year for the year like my example belowSans titre.png
and the result on two graphs below one on the left without add the +1 and the other with itSans titre2.png

OmarBenSalem
Esteemed Contributor

Re: Year plus one

stalwar1‌ ; guess from who I copied this technique? haha

Re: Year plus one

I believe you wanted to do this Omar

below(sum({<Year>}my_measure))*avg(1)

Not sure if the OP needs Above() or Below() or After() or Before(). It will depend on the sorting and pivoting of the year dimensions.

agigliotti
Honored Contributor II

Re: Year plus one

Hi,

if you select 2010 as year you'd like to get Sum( my_measure ) of 2011 ?

OmarBenSalem
Esteemed Contributor

Re: Year plus one

can't we force the aggregation to be by year ascending? directly in the expression itself?

maybe sthing like:

aggr( below(sum({<Year>}my_measure)) , (Year, ASCENDING))

?

Re: Year plus one

We sure can

OmarBenSalem
Esteemed Contributor

Re: Year plus one

If I use this :

aggr(below(sum({<Year>}Sales)) , (Year,(NUMERIC, ASCENDING)))

It does work; but when I select a Year, it correctly bring the correct values, but the other Years disappear!

How can I use this, and keep all the years even when I select a year?

OmarBenSalem
Esteemed Contributor

Re: Year plus one

Found it :

sum({<Year>}aggr(below(sum({<Year>}Sales)) , (Year,(NUMERIC, ASCENDING))))