topic Re: How do I compare current year to previous year in QlikView

How do I compare current year to previous year

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

I have a Year field in my app but values are presented as an academic year spanning two calendar years e.g. 2015-16.

How do I write an expression that shows the variance between the current selected year and the previous year?

Thanks

Greg

Re: How do I compare current year to previous year

Anonymous — Fri, 02 Sep 2016 11:24:53 GMT

Do you have date field in script on which this academic year is created?

Re: How do I compare current year to previous year

Anonymous — Fri, 02 Sep 2016 11:27:21 GMT

might be helpful?

Fiscal Year

Re: How do I compare current year to previous year

Anonymous — Fri, 02 Sep 2016 11:32:30 GMT

No. I just have a Year field I get from a csv file.

Re: How do I compare current year to previous year

Anonymous — Fri, 02 Sep 2016 11:39:23 GMT

Okay, then try like this at script?

Load

Financial_Year,

'20'&right(Financial_Year,2) as Year

From YourExcel;

Then Variance Expression would be like this?

Sum({<Year={'=$(=max(Year))'}>} Sales) - Sum({<{'=$(=max(Year)-1)'}>} Sales)

Hope this will help!!

Re: How do I compare current year to previous year

Anonymous — Fri, 02 Sep 2016 11:44:51 GMT

Thanks Balraj

Can I replace 'max' with 'currentselection'?

Re: How do I compare current year to previous year

stabben23 — Fri, 02 Sep 2016 12:36:45 GMT

Hi,

Max will reflect Your current selection, if no selection then it will be max year in Your model.

Re: How do I compare current year to previous year

Anonymous — Fri, 02 Sep 2016 13:39:21 GMT

I am having problems with the script. My field is already named Year. Do I need to change your suggested script to

Load

Year,

'20'&right(Year,2) as Year

I got syntax errors when I tried this.

Will this affect how my years are displayed in my list boxes. I don't want that. I need it to show as an academic year e.g. 2015-16.

Re: How do I compare current year to previous year

branderbwcs — Fri, 02 Sep 2016 14:21:23 GMT

Hello Greg,

The two are on the same fact table? If yes is simples, just create a calculated field year and enter the following script.

The script load creates field year in the database

Fact_Sales:

LOAD

     Date

     ,YEAR(Date) AS Year

     ,Sales

FROM Database\Sales.qvd (qvd);

The script compares the last year and previous year in the database

Sum({<

     Year={'$(=Max(Year))'}

     >} Sales)

-

Sum({<

     Year={'$(=AddYears(Max(Year),-1))'}

     >} Sales)

I have helped!

Re: How do I compare current year to previous year

Anonymous — Mon, 05 Sep 2016 08:17:21 GMT

In script table you can't have same name for more than one column, it should be unique.

You can do it like this?

Load

Year as Financial_Year,

'20'&right(Year,2) as Calender_Year

For List box presentation you can use Financial_Year, and for Logic/Expression you can use Calender_Year