topic Re: Subtract Previous Year from current Year in QlikView

Subtract Previous Year from current Year using above () and rangesum

swarup_malli — Fri, 19 Jun 2015 17:45:36 GMT

Hi,

I want to deduct previous years results from current year then divide it by previous years expression

ie (current year-previous year)/previous year

Is it possible ? in Qlikview front end ?

This is not a simple set analysis expression which deducts previous year from current .

I'm using a advanced set analysis expression here.

I'm attaching a sample qvw with some test data in it.

This can be achieved using either above () or below ( ) function plus total () .

Re: Subtract Previous Year from current Year

marcus_sommer — Sun, 21 Jun 2015 08:42:35 GMT

For this you need to remove the year-dimension from your table and then:

sum({< REPORT_YEAR = {"$(=max(REPORT_YEAR))"}>} Value) // current or max. year

sum({< REPORT_YEAR = {"$(=max(REPORT_YEAR)-1)"}>} Value) // previous year to max.year

sum({< REPORT_YEAR = {"$(=max(REPORT_YEAR))"}>} Value) /

sum({< REPORT_YEAR = {"$(=max(REPORT_YEAR)-1)"}>} Value) // ratio between max.year and previous year

See also many examples to similar topics: https://community.qlik.com/search.jspa?q=current+vs+previous

- Marcus

Re: Subtract Previous Year from current Year

sunny_talwar — Sun, 21 Jun 2015 12:32:07 GMT

Is this what you were looking for previous year column? (Not sure if result is Current Year - Previous Year, or it needs to be another calculation?)

Expression:

=RangeSum(Above(If(REPORT_YEAR = vCurrentYear, (Sum(TOTAL <MONTH_NAME> {<REPORT_YEAR={$(vPreviousYear)}>}FLIGHT_HRS)/

COUNT(TOTAL <MONTH_NAME> {<REPORT_YEAR={$(vPreviousYear)}>} AC_SERIAL) * COUNT(TOTAL <MONTH_NAME> {<REPORT_YEAR={$(vPreviousYear)}>} AC_SERIAL)) -

Above(TOTAL (Sum(FLIGHT_HRS)/COUNT(AC_SERIAL)) * COUNT(AC_SERIAL), 13)), 0, 12))

Also attaching the qvw back for your reference.

Best,

Sunny

Re: Subtract Previous Year from current Year

sunny_talwar — Sun, 21 Jun 2015 12:36:01 GMT

For calculating YoY Growth%: =([Current Year] - [Previous year]) / [Previous year]

Best,

Sunny

Re: Subtract Previous Year from current Year

swarup_malli — Mon, 22 Jun 2015 15:19:02 GMT

Thank you again !! Sunny

That was a brilliant formula you came up with using set analysis

Re: Subtract Previous Year from current Year

sunny_talwar — Mon, 22 Jun 2015 15:21:15 GMT

No problem at all

I just tweaked your formula to get you the result you were looking for. I am glad we were able to get what you wanted.

Best,

Sunny

Re: Subtract Previous Year from current Year

swarup_malli — Mon, 22 Jun 2015 16:09:28 GMT

Hey Sunny,you tweaked the formula in the following places ,I have highlighted them in red

Just curious I would appreciate it if you could elaborate on why TOTAL qualifier if being used here (TOTAL <MONTH_NAME>)

and the significance of number 13 .

=RangeSum(Above(If(REPORT_YEAR = vCurrentYear, //To get the data beside the Current column data

(Sum(TOTAL <MONTH_NAME> {<REPORT_YEAR={$(vPreviousYear)}>}FLIGHT_HRS) /

COUNT(TOTAL <MONTH_NAME> {<REPORT_YEAR={$(vPreviousYear)}>} AC_SERIAL) * COUNT(TOTAL <MONTH_NAME> {<REPORT_YEAR={$(vPreviousYear)}>} AC_SERIAL)) -

Above(TOTAL (Sum(FLIGHT_HRS)/COUNT(AC_SERIAL)) * COUNT(AC_SERIAL), 13)), 0, 12))

Re: Subtract Previous Year from current Year

sunny_talwar — Mon, 22 Jun 2015 16:18:18 GMT

Used TOTAL <MONTH_NAME> to bring the previous month's number next to current month (your set analysis made sure that I am not totaling it across different years So what you saw in previous year is what you would see next to current year.

13 was used for the same reason so that I can push the (Sum(FLIGHT_HRS)/COUNT(AC_SERIAL)) * COUNT(AC_SERIAL) this expression down 13 rows so that ultimately the same values that appeared above falls down.

I would suggest breaking your formula down into pieces and seeing their impact without the things I added. I don't know if the copy attached had the expressions disabled in them where I was doing this in pieces as well.

If it helps this could be the order of each piece

1) Sum(TOTAL <MONTH_NAME> {<REPORT_YEAR={$(vPreviousYear)}>}FLIGHT_HRS) //Try with and without TOTAL <MONTH_NAME>

2) (Sum(TOTAL <MONTH_NAME> {<REPORT_YEAR={$(vPreviousYear)}>}FLIGHT_HRS) /

COUNT(TOTAL <MONTH_NAME> {<REPORT_YEAR={$(vPreviousYear)}>} AC_SERIAL) * COUNT(TOTAL <MONTH_NAME> {<REPORT_YEAR={$(vPreviousYear)}>} AC_SERIAL)) //Again try with and without TOTAL <MONTH_NAME>

3) If(REPORT_YEAR = vCurrentYear, //To get the data beside the Current column data

(Sum(TOTAL <MONTH_NAME> {<REPORT_YEAR={$(vPreviousYear)}>}FLIGHT_HRS) /

COUNT(TOTAL <MONTH_NAME> {<REPORT_YEAR={$(vPreviousYear)}>} AC_SERIAL) * COUNT(TOTAL <MONTH_NAME> {<REPORT_YEAR={$(vPreviousYear)}>} AC_SERIAL)) //Try with and without If(REPORT_YEAR = vCurrentYear,

4) Above(TOTAL (Sum(FLIGHT_HRS)/COUNT(AC_SERIAL)) * COUNT(AC_SERIAL), 13)) // try with and without 13

You will surely learn alot in the process of breaking up your problem in small pieces.

I hope this will help.

Best,

Sunny

Re: Subtract Previous Year from current Year

swarup_malli — Mon, 22 Jun 2015 16:20:18 GMT

Thank you ! Sunny

That makes perfect sense

Re: Subtract Previous Year from current Year

sunny_talwar — Mon, 22 Jun 2015 16:21:30 GMT

Great