Solved: Re: comparing years - Qlik Community

Report Inappropriate Content · ‎2015-12-16

hey everyone,

i am creating a sale report and i am trying to compare sales between years:

my sales field = SUM(NIS_TOTPRICE)

and year = Year,

can someone please help me?

brunobertels · ‎2015-12-17

Thanks for sharing

At the end it's working

see below

ILS Total give the whole total for all the years and total for a selected year

Set analysis ( Year) i changed it like that : Num(sum ({$<year={}>}NIS_TOTPRICE))

but as is this formula is wrong because "year" here is interpreted as qlik syntax keyword (blue) and not like a dimension.(orange)

so without year selection it gives you the same result as ILS Total

selecting a year it will give you the total for the year selected

if ever you always want the total even if a year is selected use this :

Num(sum ({<YEAR=>}NIS_TOTPRICE))

PAST YEAR i changed it like that : sum ({$<YEAR={'$(=max(YEAR-1))'}>}NIS_TOTPRICE)

so without any year selection it will give you the total for 2013 if your data end in 2014 and so on

selecting a specific year eg: 2010 , it will give you the total for 2009 etc

i can't send you back your modified app for it is two heavy to upload it

hope it is what you need especialy for PAST YEAR

Bruno

View solution in original post

sunny_talwar · ‎2015-12-16

What is the exact issue that you are facing? You seem to have all the piece you have to compare the sales

Report Inappropriate Content · ‎2015-12-16

Hey Sunny i am trying to make an expression for a report

thakkarrahul01 · ‎2015-12-16

Hi,

There are more than one way to do it, you can make some changes in data model to simplify your set analysis expressions as discussed in detail Year-over-Year Comparisons

Another approach you can take it do comparison in set analysis of the expression. For that you have to give range of months you want to include while calculating, in your case it can be last 12 months or years as 2013/2014/2015 so on.

You can find some more details in Range of month in a bar chart . It is for bar charts but your expression will remain same for straight table as well.

I hope this helps !

reddy-s · ‎2015-12-16

Hi Jhonatan,

I am sorry but the image is not clear. But when you would like to compare a measure between 2 years, make use of a set analysis expression to do so.

Report Inappropriate Content · ‎2015-12-17

Hey everyone ,

o.k i got this to show me the year on:

(sum ({$<Year={'YEAR'}>}NIS_TOTPRICE))

NIS_TOTPRICE=sales

i am trying to show the sales of the past year

lets say (sum ({$<Year={'YEAR'}>}NIS_TOTPRICE))

show me the year that has been choseen for example 2014

and i want to see 2013 sales...

i need some help with the syntax...

brunobertels · ‎2015-12-17

Hi try like this

past year :

sum ({$<Year={'$(YEAR)-1'}>}NIS_TOTPRICE)

bruno

Report Inappropriate Content · ‎2015-12-17

tried it but doesn't work....

i also tried defining it with

Num( sum ({$<Year={'$(YEAR)-1'}>}NIS_TOTPRICE))

but did not work as well...

brunobertels · ‎2015-12-17

Hi

Try this

sum ({$<Year={$(=max(Year-1))}>}NIS_TOTPRICE))

where Year is your dimension

Chek also your syntax lower / upper Case

Try also for your set analysis this amazing tool :

http://tools.qlikblog.at/SetAnalysisWizard/QlikView-SetAnalysis_Wizard_and_Generator.aspx?sa=

Bruno

Report Inappropriate Content · ‎2015-12-17

doesn't work...

this is has work:

(sum ({$<Year={'YEAR'}>}NIS_TOTPRICE))

but that doesn't :

sum ({$<Year={$(=max(Year-1))}>}NIS_TOTPRICE))

....