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

[swarup_malli]

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 ()  .

[marcus_sommer]

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

[sunny_talwar]

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

[sunny_talwar · Accepted solution]

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

Best,

Sunny

[swarup_malli]

Thank you again !! Sunny

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

[sunny_talwar]

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

[swarup_malli]

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

[sunny_talwar]

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

[swarup_malli]

Thank you ! Sunny

That makes perfect sense

[sunny_talwar]

Great