Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

kathy_parker
New Contributor

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.

Tags (2)
1 Solution

Accepted Solutions
dan_sullivan
Contributor II

Re: Vdate needed for prior YTD

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

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

9 Replies
dan_sullivan
Contributor II

Re: Vdate needed for prior YTD

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)

kathy_parker
New Contributor

Re: Vdate needed for prior YTD

vCurrentDate = =Date#(Max(ValuationDate))

dan_sullivan
Contributor II

Re: Vdate needed for prior YTD

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

kathy_parker
New Contributor

Re: Vdate needed for prior YTD

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
Contributor II

Re: Vdate needed for prior YTD

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

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

kathy_parker
New Contributor

Re: Vdate needed for prior YTD

I used

=AddMonths(Max(ValuationDate),-12)

and DID get a value!  Thank you!

kathy_parker
New Contributor

Re: Vdate needed for prior YTD

One more thing tho

=AddMonths(Max(ValuationDate),-12)

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

dan_sullivan
Contributor II

Re: Vdate needed for prior YTD

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)

kathy_parker
New Contributor

Re: Vdate needed for prior YTD

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/>