Prior month, final day

[Anonymous]

Help please!

I am trying to return summed data in Qlik Sense for the prior month based on a selected month.

Background

I have a field called STATUS which has various entries, one of which is NEW BUSINESS and one is INFORCE. Each of these have Net Written Premium figures against them. The NEW BUSINESS entries can be against any day in a month whereas the INFORCE entries are all set to the last day of each month.

Problem

When the NEW BUSINESS Net Written Premium data is selected I need to return the INFORCE Net Written Premium from the prior month.

STATUS	Row Extract Date	Net Written Premium
INFORCE	31/01/2018	£100
INFORCE	31/01/2018	£200
NEW BUSINESS	14/01/2018	£50
NEW BUSINESS	20/01/2018	£30
INFORCE	31/12/2017	£300
INFORCE	31/12/2017	£25

Using the data above, if January 2018 was selected, £80 would show as NEW BUSINESS and £325 would show as INFORCE.

Any help will be gratefully received!

Thanks,

Mitch

[Anonymous]

Hi Mitchell,

Include the statement below in your set analysis for the IN FORCE measure. This will subtract one from the currently selected month.

[Row Extract Date] = {'$(=AddMonths(Only(Date),-1))'}

Thanks,

Ralph