topic Re: compare current year previous year in QlikView

compare current year previous year

Sun, 27 Apr 2014 19:24:26 GMT

I want to compare the current year with previous year

for example

its booked until 201404

i want t show the sales for sum(maxyear) : Jan/Feb/Mar/Apr 2014

and compare it with sum(sales) : Jan/Feb/Mar/Apr 2013

Re: compare current year previous year

ashwanin — Sun, 27 Apr 2014 20:13:59 GMT

below thread is based on same, Please follow same. I

Comparision vs. Previous Period

Re: compare current year previous year

its_anandrjs — Sun, 27 Apr 2014 20:25:14 GMT

Write like below script

If dimension you use Year

Dimension:- Year

Expression for current year:- Sum({<YearField = {'$(=Year(Today()))'}>} sales)

Expression for previous year:- Sum({<YearField = {'$(=Year(Today())-1)'}>} sales)

If You have dimension any Date field

Expression for Current Year:- =SUM({$<DateField = {'>=$(=YearStart(Today()))<=$(=MonthEnd(Today()))'}>}sales)

Expression for Previous Year:- =SUM({$<DateField = {'>=$(=AddMonths(YearStart(Today()),-12))<=$(=AddMonths(MonthEnd (Today()),-12))'}>}sales)

Re: compare current year previous year

MK_QSL — Sun, 27 Apr 2014 20:47:14 GMT

Considering you have Date field in your data model

Current Year

=SUM({$<Date = {'>=$(=YearStart(Today()))<=$(=MonthEnd(Today()))'}>}Sales)

Previous Year

=SUM({$<Date = {'>=$(=YearStart(AddYears(Today(),-1)))<=$(=MonthEnd(AddYears(Today(),-1)))'}>}Sales)

Comparison/Growth you can do as

([Current Year]-[Previous Year])/[Previous Year]

Hope this helps...

Re: compare current year previous year

Sun, 27 Apr 2014 21:13:02 GMT

Easiest thing to do it? Get your "date" field, and create another two fields in the script:

MONTH(date) as Month,

YEAR(date) as Year

Next step, on your dashboard, create a graphic sum table (you'll see when you choose the graphic type) with eveything you want, if you want to lock the selections for 2013 and 2014, use two expressions, with the set analysis (friends have already answered that part).

My tip: just do a normal sum(sales). Create two lists, one with month and one with year, and in their properties, in the "Presentation" tab, change the selection style to "LED check boxes". Those are user friendly.

Whenever they pick 2013, 2014, 2013 and 2014, it will show the sum(sales) of that range. If they want to see for only one month, etc, etc, this way it will become also possible.

This way you will not need to keep changing for every selection people want.

Regards,

Julio

Re: compare current year previous year

stevedark — Sun, 27 Apr 2014 22:18:39 GMT

You may want to take a look at the example application I have uploaded: QlikView App: Set Analysis - Prior Period Comparison

This looks at year on year as well as prior month and month to date comparisons.

Hope it is helpful.

Steve

Re: compare current year previous year

Anonymous — Fri, 15 Dec 2017 09:36:30 GMT

what do you mean by yearstart,addyears and monthend??

is this a column name ?

Re: compare current year previous year

stevedark — Fri, 15 Dec 2017 09:59:46 GMT

These are Qlik functions. YearStart will return the 1st Jan 00:00:00 for whichever year the date in the parameter is for, so;

=YearStart(today())

Will return;

01/01/2017 00:00:00

The other functions work in a similar way.

Steve

Re: compare current year previous year

its_anandrjs — Fri, 15 Dec 2017 10:06:31 GMT

Hi,

This functions yearstart,addyears and monthend Qlik Date functions

YearStart :- This gives Year start date that i 01/01/2017

Addyears :- This adding years to the current year

Monthend :- this gives Monthend date of the Month for current month it is 31/12/2017.

Re: compare current year previous year

Anonymous — Fri, 15 Dec 2017 10:24:39 GMT

i am using this below expressions i am getting following:-

i am not geeting current sales

Current Year

=SUM({$<Date = {'>=$(=YearStart(Today()))<=$(=MonthEnd(Today()))'}>}Sales)

Previous Year

=SUM({$<Date = {'>=$(=YearStart(AddYears(Today(),-1)))<=$(=MonthEnd(AddYears(Today(),-1)))'}>}Sales)

Comparison/Growth you can do as

([Current Year]-[Previous Year])/[Previous Year]

Re: compare current year previous year

stevedark — Fri, 15 Dec 2017 10:36:03 GMT

Create a table with those expressions with Month Year as the dimensions, you can then see what is going on.

Add an additional measure to this table of just SUM(Sales), this will give you the amounts you are after in each month.

Try building up the set analysis a bit at a time; e.g. just do the >=YearStart first.

In fact, unless you have future sales values you don't need the <= on the Current Year values.

Set analysis can be quite funny about date formats. You may need to use the Date function to format the output from YearStart and MonthEnd to be in the same format as your Date field.

Hope that helps.

Steve

Re: compare current year previous year

its_anandrjs — Fri, 15 Dec 2017 10:39:46 GMT

The expressions that you use is ok for me please check what the Steve suggested to you, may be doing some mistake in set analysis.

Re: compare current year previous year

Anonymous — Fri, 15 Dec 2017 10:43:28 GMT

you mean inline table?

Re: compare current year previous year

stevedark — Fri, 15 Dec 2017 11:11:14 GMT

Just a straight table chart. You could copy your existing one and change the dimension.

Re: compare current year previous year

Anonymous — Fri, 15 Dec 2017 11:28:52 GMT

now its functioning well but ,if i want to compare 2015 sales with 2017 in same chat .here only 2016 AND 2017 SALES CAN BE SEEN WHEN YEAR DIMENSION WAS TAKEN

Re: compare current year previous year

stevedark — Fri, 15 Dec 2017 12:25:44 GMT

You need another expression with AddYears and -2 in there as the parameter.

Re: compare current year previous year

Anonymous — Mon, 18 Dec 2017 03:55:20 GMT

i have written expression on 2016 label as following :-

=num(sum({< sold_Year = {"$(=Max(sold_Year))"}>} [inv value]/Sales_INR_Unit),'#,##0.0')

now you want me to add another expression?

Re: compare current year previous year

stevedark — Mon, 18 Dec 2017 08:23:35 GMT

Sorry, I read it as you wanted to show three years in separate columns. Is it that you want to keep current year ffixed, but change which year you compare to?

If so, you will need to change the years back value to a variable, say vYB and insert that into the expression like this:

=SUM({$<Date = {'>=$(=YearStart(AddYears(Today(),-$(vYB))))<=$(=MonthEnd(AddYears(Today(),-$(vYB))))'}>}Sales)

This will need to be created under Settings \ Variables, and then controlled by a pair of buttons. See this post for details on changing variables with buttons:

https://www.quickintelligence.co.uk/qlikview-buttons/

Hope that helps.