Announcements
July 15, NEW Customer Portal: Initial launch will improve how you submit Support Cases. IMPORTANT DETAILS
cancel
Showing results for
Did you mean:
Specialist

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

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

1 Solution

Accepted Solutions
MVP

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

Best,

Sunny

9 Replies

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

- Marcus

MVP

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

MVP

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

Best,

Sunny

Specialist
Author

Thank you again !! Sunny

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

MVP

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

Specialist
Author

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

MVP

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

Specialist
Author

Thank you ! Sunny

That makes perfect sense

MVP

Great

Community Browser