Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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')
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
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))
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))
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.