topic comparing years in App Development

comparing years

Wed, 16 Dec 2015 10:34:21 GMT

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?

Re: comparing years

sunny_talwar — Wed, 16 Dec 2015 10:36:46 GMT

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

Wed, 16 Dec 2015 11:01:49 GMT

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

Re: comparing years

thakkarrahul01 — Wed, 16 Dec 2015 11:45:23 GMT

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

reddy-s — Wed, 16 Dec 2015 13:04:25 GMT

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

Thu, 17 Dec 2015 08:54:27 GMT

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

brunobertels — Thu, 17 Dec 2015 09:14:20 GMT

Hi try like this

past year :

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

bruno

Re: comparing years

Thu, 17 Dec 2015 09:27:36 GMT

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

brunobertels — Thu, 17 Dec 2015 09:52:59 GMT

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

Thu, 17 Dec 2015 11:06:16 GMT

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

brunobertels — Thu, 17 Dec 2015 11:15:48 GMT

ok sorry to read that

can you share a sample of your data or the qvf

Regards

Bruno

Re: comparing years

Thu, 17 Dec 2015 11:20:30 GMT

ofcurse what is your mail?

Re: comparing years

brunobertels — Thu, 17 Dec 2015 11:51:07 GMT

you can upload your app here

clic on advance user editor and join link

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

Bruno

Re: comparing years

Thu, 17 Dec 2015 12:02:31 GMT

Here is a Link:

קלינטון המעודכן ביותר

Re: comparing years

brunobertels — Thu, 17 Dec 2015 13:21:21 GMT

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

Thu, 17 Dec 2015 13:33:55 GMT

Bruno thank you very much!!!

you helped me alot!!

Re: comparing years

Sun, 20 Dec 2015 09:13:08 GMT

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

brunobertels — Mon, 21 Dec 2015 09:49:47 GMT

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