topic previous year ytd calculation in QlikView

previous year ytd calculation

rajtechnocraft — Tue, 08 Sep 2009 17:02:01 GMT

Hi All

i want to calculte previous YTD sale

this calculation will be financial year wise based.

my financial year is starts from october - september

suppose i select fin_year 2008-2009 july then previous yeartytd should calculate sale upto july 2007-2008

require this help urgently.

previous year ytd calculation

Tue, 08 Sep 2009 17:12:04 GMT

I asuume your list box or rolling month looks someting like Sep-2009
When you are loading the script it looks like
Load
date(monthstart(Date),'MMM-YYYY') as RollingMonth,
etc
FROM table x;

You can then create two variables

vDate1=date(addmonths(RollingMonth,-11),'MMM-YYYY')
vDate2=RollingMonth

Your list box or selection field is based on RollingMonth

Then you can use the following expression in the report/chart

=sum({$<RollingMonth={">=$(#vDate1)<=$(#vDate2)"}>} Value)

So no matter what month-year you select the expression will alway total 12 months of data i.e. if you select Feb-2009 it will sum Mar-2008 to Feb-2009 etc

previous year ytd calculation

piotr_kierul — Tue, 08 Sep 2009 17:16:46 GMT

In the scrip you should add a financial date dimmention. For example:

AddMonths(Date,-3) as Financial_Date

then add Financial_Year and Financial_Month fields based on Financial_Date.

The Set Analysis expression based on those fields should be:

{$<Financial_Year={'$(=(Financial_Year)-1)'},Financial_Month={"<=$(#=Num(Financial_Month))"} >}

Regards.