How do I compare current year to previous year

[Anonymous]

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

[Anonymous]

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

[Anonymous]

might be helpful?

Fiscal Year

[Anonymous]

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

[Anonymous]

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

[Anonymous]

Thanks Balraj

Can I replace 'max' with 'currentselection'?

[stabben23]

Hi,

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

[Anonymous]

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.

[branderbwcs]

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!

[Anonymous]

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