How to calculate prior month

[Diere29]

I am trying to calculate the prior month for a dataset where they want to view all months consistently at once, the table below is what I am aiming to achieve. The current Formula I have brings back the values for the current month and I cannot work out why.

Table example:  

Month & Year	Valued Cost	Valued Cost Prior Month
Oct 2023	48,835,990	42,252,290
Nov 2023	50,469,069	48,835,990
Dec 2023	35,151,439	50,469,069
Jan 2024	37,325,215	35,151,439
Feb 2024	35,210,811	37,325,215
Mar 2024	36,598,001	35,210,811
Apr 2024	21,395,509	36,598,001
May 2024	39,601,608	21,395,509

Formula:

Num(SUM(
 {<
Date = {">=$(AddMonths(Min(Date), -1))<=$(AddMonths(max(Date), -1))|"}>}
{<
Metric_Days={'Total Valued Cost'}>} [IP_INVFIFO.VALUE]),'#,##0')

[hic]

Set analysis is not the right tool for this. Try Above() instead.

See https://community.qlik.com/t5/Design/The-Above-Function/ba-p/1465357

[Diere29]

This worked as expected but when I place it into a pivoted table it doesn't operate and return's Zeros. 

RangeSum(Above(Sum({<Metric_Days={'Total Valued Cost'}>} [IP_INVFIFO.VALUE]), 1))

[Anil_Babu_Samineni]

With Pivot you might require TOTAL qualifier as that is column level split.

RangeSum(Above(TOTAL Sum({<Metric_Days={'Total Valued Cost'}>} [IP_INVFIFO.VALUE]), 1))

[Anil_Babu_Samineni]

RangeSum(Above(TOTAL Sum({<Metric_Days={'Total Valued Cost'}>} [IP_INVFIFO.VALUE]), 1))

[neerajthakur]

Try using peek and previous if above is not working.

If(RowNo() = 1, Null(), Previous([Valued Cost])) as [Valued Cost Prior Month]

This may also work on frontend

If(RowNo() = 1, Null(), Above(Sum([Valued Cost]), 1))

It handles first column too if nothing is available.
Try creating this on backend, other wise it should work well in Qlik Table.

[Diere29]

I am running into the following error and wondering if you have some insight on why this could be possibly occurring.  

LM total Actual Cost test: When I use this measure it doesn't work and the rows results come up as null

Only(Aggr(Above(Sum({<Metric_Days={'Total Actual Cost'}>} [UK_IP_INVFIFO.VALUE])),Month,Year))

LM total Actual Cost test (1): this measure works but December appears as null 

/Above(Sum({<Metric_Days={'Total Actual Cost'}>} [UK_IP_INVFIFO.VALUE]),1,12 )
 Values retracted for privacy reasons but you get the image provided to show what i am saying

[Kushal_Chawda]

@Diere29  try below

=sum(aggr(Above(Sum({<Metric_Days={'Total Actual Cost'}>} [UK_IP_INVFIFO.VALUE]),Year,Month))