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