Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 () .
For calculating YoY Growth%: =([Current Year] - [Previous year]) / [Previous year]
Best,
Sunny
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
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
For calculating YoY Growth%: =([Current Year] - [Previous year]) / [Previous year]
Best,
Sunny
Thank you again !! Sunny
That was a brilliant formula you came up with using set analysis
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
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))
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
Thank you ! Sunny
That makes perfect sense
Great