Skip to main content
Announcements
Qlik Community Office Hours, March 20th. Former Talend Community users, ask your questions live. SIGN UP
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/>