Skip to main content
Announcements
July 15, NEW Customer Portal: Initial launch will improve how you submit Support Cases. IMPORTANT DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
swarup_malli
Specialist
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.

Current minus Previous.png

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

1 Solution

Accepted Solutions
sunny_talwar

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

Capture.PNG

Best,

Sunny

View solution in original post

9 Replies
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?)

Capture.PNG

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

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

Capture.PNG

Best,

Sunny

swarup_malli
Specialist
Specialist
Author

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

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
Specialist
Specialist
Author

Thank you ! Sunny

That makes perfect sense

sunny_talwar

Great