Find (and subtract) value based on month

[AD_Accel]

Hi all,

Fairly new to Qlik Sense - I have searched high and low for a similar issue on the forums, but cannot find anything that resembles this.

I have loaded a table with data that looks as follows:

ID	ID_PolicyYear_DevMonth	DevMonth	Reporting Period	Total Paid
6F6JL3	6F6JL3_2017_19	19	31/07/2018	£7,846.36
6F6JL3	6F6JL3_2017_20	20	31/08/2018	£7,846.36
6F6JL3	6F6JL3_2017_21	21	30/09/2018	£7,032.95
6F6JL3	6F6JL3_2017_22	22	31/10/2018	£7,032.95
B1311	B1311_2017_18	18	31/07/2018	£0.00
B1311	B1311_2017_19	19	31/08/2018	£100.00
B1311	B1311_2017_20	20	30/09/2018	£435.50
A63GB	A63GB_2017_1	1	31/07/2018	£500.00
A63GB	A63GB_2017_2	2	31/08/2018	£1,000.00

ID_PolicyYear_DevMonth represents a unique identifier for each Reporting Month's position, and corresponds with its ID. The Total Paid is current position, and DevMonth reflects the number of months from the point of which the record first appeared (inclusive).

I am trying to find the previous month's Total Paid and return it in line as Last Month's Total Paid - example below. If there is no earlier record in the data, or it is the first record (i.e. DevMonth 1), it should mirror the Total Paid in row:

ID	ID_PolicyYear_DevMonth	DevMonth	Reporting Period	Total Paid	Last Month's Total Paid
6F6JL3	6F6JL3_2017_19	19	31/07/2018	£7,846.36	£7,846.36
6F6JL3	6F6JL3_2017_20	20	31/08/2018	£7,846.36	£7,846.36
6F6JL3	6F6JL3_2017_21	21	30/09/2018	£7,032.95	£7,846.36
6F6JL3	6F6JL3_2017_22	22	31/10/2018	£7,032.95	£7,032.95
B1311	B1311_2017_18	18	31/07/2018	£0.00	£0.00
B1311	B1311_2017_19	19	31/08/2018	£100.00	£0.00
B1311	B1311_2017_20	20	30/09/2018	£435.50	£100.00
A63GB	A63GB_2017_1	1	31/07/2018	£500.00	£500.00
A63GB	A63GB_2017_2	2	31/08/2018	£1,000.00	£500.00

I have tried this a number of ways without success (NB - the Lookup function below is written to recreate the previous month's  ID_PolicyYear_DevMonth) :

Spoiler

Temp1:

Load

ID_PolicyYear_DevMonth,


Lookup('Total Paid','ID_PolicyYear_DevMonth',"Claim Policy Number" & '_' & "Risk Inception Date Year" & '_' & ("Claim Development Month (UW Year)"-1),'ClaimsLoad') as "Last Month's Total Paid"


// Peek("Total Paid",-1,'ClaimsLoad') as "Last Month's Total Paid"


// Previous("Total Paid") as "Last Month's Total Paid"

Resident ClaimsLoad

Order By ID_PolicyYear_DevMonth;

Lookup works partially when I use as within a temporary table (resident load), but in some cases returns the correct value multiplied by 2 - it also takes forever to run.

Peek  returns one of two values for every record; £62,500 or £125,000.

Previous  works partially - but like Peek, it returns doubled values on occasion, and it also returns values for rows I don't want, due to looking only at row number (I accept I need to include some additional qualifying criteria here).

Thanks in advance for any advice you can offer.

[tresesco · Accepted solution]

I am not sure if I understood the issue you wanted to highlight. However, the below code gives the output you sought.

If(ID=Peek(ID), Peek([Total Paid]), [Total Paid]) as [Last Month's Total Paid]

[AD_Accel]

Thanks Tresesco

This did not quite work for me initially, but I believe it's because there is a flaw in the earlier table - conceptually, this should work.

Thanks again!