Qlik Community

Qlik Sense App Development

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

Announcements
BARC’s The BI Survey 19 makes it official. BI users love Qlik. GET REPORT
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
Partner
Partner

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

View solution in original post

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

Partner
Partner

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

View solution in original post

aurelie_potvin
New Contributor II

Re: Get Sales vs Last Year Sales for each year

Thanks Shraddha, that worked.