Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

aurelie_potvin
New Contributor II

Get Sales vs Last Year Sales for each year

Hi experts,

I have following information in my table

  • Region (with 3 regions A, B & C)
  • Month (ex. 31/07/17 formatted as Jul-17)
  • Year (ex. 2017)
  • Sales $

  

In my report, I have a filter that lets me choose my year and now,I would like to show 6 KPI's which are the sales for each region, both for the chosen year as for the "chosen year -1".

I managed to set the 3 KPI's for the sales per region, per year but I cannot find a solution for the previous year.

  • Sales for Region A for chosen year : Avg({$<[Region]={"A"}>}[Sales])
  • Sales for Region A for chosen year -1 : ?

Any idea how I could do this?

Thanks in advance for your help & time

1 Solution

Accepted Solutions
shraddha_g
Honored Contributor III

Re: Get Sales vs Last Year Sales for each year

please provide sample app.

According to info you have provided

you can try Sum({<[Region]={"A"},Year = {"$(=max(Year)-1)"}>}[Sales]) for Sales for Region A for chosen year -1

and for Sales for Region A for chosen year, you can use Sum({<[Region]={"A"},Year = {"$(=max(Year))"}>}[Sales])

3 Replies
brianrmacdonald
Contributor

Re: Get Sales vs Last Year Sales for each year

There are a couple of ways to go about this depending on the structure of your data.  You might have a look at the Above function if your data is ordered that way.  Another way would be to build a table at load time that is keyed to your main table but that has last years sales in it.

HTH

shraddha_g
Honored Contributor III

Re: Get Sales vs Last Year Sales for each year

please provide sample app.

According to info you have provided

you can try Sum({<[Region]={"A"},Year = {"$(=max(Year)-1)"}>}[Sales]) for Sales for Region A for chosen year -1

and for Sales for Region A for chosen year, you can use Sum({<[Region]={"A"},Year = {"$(=max(Year))"}>}[Sales])

aurelie_potvin
New Contributor II

Re: Get Sales vs Last Year Sales for each year

Thanks Shraddha, that worked.