17 Replies Latest reply: Dec 21, 2015 4:49 AM by bruno bertels

# comparing years

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,

• ###### Re: comparing years

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

• ###### Re: comparing years

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

• ###### Re: comparing years

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.

• ###### Re: comparing years

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 !

• ###### Re: comparing years

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

• ###### Re: comparing years

Hi try like this

past year :

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

bruno

• ###### Re: comparing years

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

• ###### Re: comparing years

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

• ###### Re: comparing years

doesn't work...

this is has work:

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

but that doesn't :

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

....

• ###### Re: comparing years

ok sorry to read that

can you share a sample of your data or the qvf

Regards

Bruno

• ###### Re: comparing years

ofcurse what is your mail?

• ###### Re: comparing years

clic on advance user editor and join link

or send me it bruno.bertelsatcegetel.net ( change at by @)

Bruno

• ###### Re: comparing years

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

• ###### Re: comparing years

Hey Bruno is there is a chance you can help me figure out why this if condition doesn't work?

if sum({\$<YEAR={'\$(=max(YEAR-1))'}>}RIV_QUANT)>0 and sum({\$<YEAR={'\$(=max(YEAR-1))'}>}NIS_TOTPRICE)>0 and (sum ({\$<Year={'YEAR'}>}RIV_QUANT))<1 and (sum ({\$<Year={'YEAR'}>}NIS_TOTPRICE))<1,

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

RIV_QUANT= quantatiy

NIS_TOTPRICE=SALES

• ###### Re: comparing years

Hi

Sorry i don't know how to achieve this.

Your mesure seems wrong and should be :

if (

sum({\$<YEAR={'\$(=max(YEAR)-1)'}>}RIV_QUANT)>0

and sum({\$<YEAR={'\$(=max(YEAR)-1)'}>}NIS_TOTPRICE)>0

and sum ({\$<YEAR={'\$(=max(YEAR))'}>}RIV_QUANT)<0

and sum ({\$<YEAR={'\$(=max(YEAR))'}>}NIS_TOTPRICE)<0,

sum ({\$<YEAR={'\$(=max(YEAR))'}>}NIS_TOTPRICE),0)

I tested it but it does'nt work. I'am not sure If & And are the solution here.

Good luck and let me know if you find a solution , I am a little bit curious on how it can be solved.

bruno

###### Bruno thank you very much!!!

you helped me  alot!!