Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have a dataset (see simple example below) that I need to calculate YoY metrics (calculation: current year divided by last year minus 1) by any combination of country, region, Year, qtr, and month. For example, I'd like to have filters for country, region, year, qtr, and month and then create a KPI chart that showed the YoY % growth. For example, if I chose US->Central->2016->Q1->Nov from filters, I would get a YoY % decline of -50% ($5/$10-1). If I chose LAC->Brazil->2016->Q1 from filters, I would get a YoY % decline of -33% ($30/$45-1). Keep in mind that I will have many years in this dataset, so it needs to be dynamic.
Country | Region | Fiscal Year | Fiscal Qtr | Month | $ |
US | Central | 2015 | Q1 | Nov | 10 |
Dec | 15 | ||||
Jan | 20 | ||||
2016 | Q1 | Nov | 5 | ||
Dec | 10 | ||||
Jan | 15 | ||||
LAC | Brazil | 2015 | Q1 | Nov | 10 |
Dec | 15 | ||||
Jan | 20 | ||||
2016 | Q1 | Nov | 5 | ||
Dec | 10 | ||||
Jan | 15 |
Friend
Try this
@ !
Check more on community related to YOY.
Or if you can, upload a sample file/data?
Thanks.
this appears to be in QlikView, however, I am working in QlikSense. Can you send in QlikSense?
attached is sample data
It's for both.
I believe in above links they are giving an example script, which will be same in Qlik Sense and Qlikview.
The front end will be different but expressions/script is same.
You can check the attached qvf.
I'm not sure about the expression but the data model has been made for you with the help of Master Calendar.
If you find my expression or the result as in correct, you can further edit it or maybe someone else can?
Thanks.
Thanks Shahbaz...unfortunately the expression is not yielding the right number.
I guessed it, the expression might not be what you need.
What result are you expecting or do you have an expression already?
see my original post for calculation and examples...thx!