Calculating Prior Year Same Month Sales Data in Qlik Sense

[Diere29]

I’m trying to calculate the prior year’s sales data for the same month in Qlik Sense, but I’m running into some challenges. Here’s my current setup:

Data Structure: All sales for each month are grouped by the month-end date. For example, all sales for May 2024 are recorded under the date "31st May 2024." There are no daily sales-level transactions, just one total per month.

Display Format: The data is displayed in a vertical list, cascading downwards by month (e.g., Jan 2022, Feb 2022, Mar 2022, etc.).

Total Actual Cost LY =
Sum({
$<
Metric_Days={'Total Actual Cost'},
Date={"$(vPrevMonth)"}
>} [UK_IP_INVFIFO.VALUE])

• vPrevMonth = Date(AddMonths($(vCurrMonthStart), -12), 'MM-YYYY')
• vCurrMonthStart = Date(Floor(MonthStart(Max(Date))), 'DD-MM-YYYY')



  Any help on how to fix this is greatly appreciated. 

  Table example of what i am trying to achieve 
  
  

  Month	current year sales	Prior year sales	Diff
  Jan-23	9898	0	9898
  Feb-23	7777	0	7777
  Mar-23	7776	0	7776
  Jan-24	7865	9898	-2033
  Feb-24	7534	7777	-243
  Mar-24	6790	7776	-986

[Kushal_Chawda]

@Diere29  If you have Month as dimension set analysis won't work. You need to use above function. Assuming your MonthYear field is formatted as Numeric. If not you need to first format it.

=sum(aggr(above(Sum({$<Metric_Days={'Total Actual Cost'}>} [UK_IP_INVFIFO.VALUE]),12), (MonthYear,(NUMERIC)))

[Qrishna · Accepted solution]

Try below if you just have MonthYear as Dim:

Dim: MonthYear
Measure1: cy = sum(Sales)
Measure2: py_samemonth = if(not isnull(Above(cy, 12)), Above(cy, 12), 0)
Measure3: diff = cy-py_samemonth

if you have multiple dims, do as above using Aggr() function.

[Diere29]

Thank you so much this worked perfectly 🙂