YTD(Year to date)

[#1]

Hi All,

I have Ratio say sum(amount_paid)/sum(confirmed_policy_premium) and Dimension as a Year.

I want to the ratio for all the years. This works fine.

But i have a scenario where if Current month is September then Current Years will be calculated from January to September and other Previous years from January to December.

I want other Years to be calculated from January to September.

How to achieve this.

Please help me with this. I don't have QVW to attach.

Thanks & regards,

Keerthi

[vikasmahajan]

Please go through this qvw.

Vikas

[hic · Accepted solution]

I would create a YTD flag in my master calendar in the script:

     If( Date - YearStart(Date) <= Today() - YearStart(Today()), 1, 0 ) as IsInYTD

This flag can then be used in a chart expression:

     Sum( {$<IsInYTD={1}>} Amount )

I can use any dimension; e.g. Year, Month, Product and the expression will still work. I can compare YTD between years this way.

HIC

[#4]

Hi Henric,

Say if i have 14-Feb-2014 i.e today's date and my dimension is Year and expression is loss_ratio.

When use the Sum( {$<IsInYTD={1}>} loss_ratio).

Will i get the other years 2012, 2013 loss_ratio calculated only till 14-Feb-2013 and 14-Feb-2012 ???

Thanks & Regards,

Soumya

[hic]

Yes. The IsInYTD will flag if the date is before Feb 14, irrespective of which year it is.

HIC

[#6]

Thanks a lot

[#7]

Hi Henric,

Excelent answer.This way is better useful for regular  requirements.it gives better performance than writing

functions in UI side expression.

am i right?

can you please share me any docs like regular usage MTD and YTD and like FTD.i want to implement because helpful at the time optimization.

regards

Mahesh

[hic]

Not sure there are any such documents... but I am planning a blog post on the topic.

Basically I always create a set of flags in my master calendar: IsCurrentMonth, IsLastMonth, IsInYTD, etc, and then I use these in Set Analysis. Much cleaner and simpler formulae.

HIC

[#9]

Thank you for your reply.We hope,expecting soon from such type of   post from your blog.

regards

Mahesh T

[stevedark]

I've uploaded a couple of documents showing point in time comparison which may be of interest to you:

QlikView App: Simple Year To Date Example (Set Analysis)

QlikView App: Set Analysis - Prior Period Comparison

Hope they are useful to you.

Steve