Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Vdate needed for prior YTD

To get current year YTD inforce value, I use this formula:

Sum({$<ValuationDate={'$(vCurrentDate)'}>}Inforce)

How would get the YTD inforce value for the previous year?

Valuation Date =

1/31/2016

2/28//2016

3/31/2016

4/30/2016

etc - the inforce values are measured by the last day of the month.

1 Solution

Accepted Solutions
dan_sullivan
Creator II
Creator II

Is the vPYTD returning a value if you put in a KPI by itself?  If not try this formula

vPYTD = AddMonths(Max(ValuationDate),-12)

View solution in original post

9 Replies
dan_sullivan
Creator II
Creator II

Please post your formula you are using to create the variable 'vCurrentDate'.  If it as simple as vCurrentDate = MonthEnd(Today()) then create another variable vPYTD = AddMonths(MonthEnd(Today()),-12) then your set analysis will look like this: Sum({$<ValuationDate={'$(vPYTD)'}>}Inforce)

Anonymous
Not applicable
Author

vCurrentDate = =Date#(Max(ValuationDate))

dan_sullivan
Creator II
Creator II

vPYTD = AddMonths($(vCurrentDate),-12)   does that work for you?

Anonymous
Not applicable
Author

I added the vPYTD as =AddMonths($(vCurrentDate),-12) 

and used this formula

Sum({$<ValuationDate={'$(vPYTD)'}>}Inforce)

but it did not return a value.

dan_sullivan
Creator II
Creator II

Is the vPYTD returning a value if you put in a KPI by itself?  If not try this formula

vPYTD = AddMonths(Max(ValuationDate),-12)

Anonymous
Not applicable
Author

I used

=AddMonths(Max(ValuationDate),-12)

and DID get a value!  Thank you!

Anonymous
Not applicable
Author

One more thing tho

=AddMonths(Max(ValuationDate),-12)

I need this for (Inforce) only.  How would I incorporate Inforce?

dan_sullivan
Creator II
Creator II

I'm assuming Inforce is a dimension then?  Use set analysis on the dimension and the dimension value.

=AddMonths(Max({$<[Dimension For Inforce]={'Inforce'}>}ValuationDate),-12)

Anonymous
Not applicable
Author

Dan, This did not return a value for me.

vPYTD=AddMonths(Max({$<[Inforce]={Inforce}>}ValuationDate),-12) returns $0 value

Do you see anything I should change in this formula?

Kathy Parker | Manager, PMO and Document Management, | LSSGB

d 601.936.3250

2305 Lakeland Drive, Flowood, MS

ampublic.com<http://www.ampublic.com/>