topic Re: YTD accumulation in QlikView

YTD accumulation

Sun, 19 Jun 2016 16:14:45 GMT

Hi All,

I am calculating YTD accumulation for current year ,previous year and showing forecast and target value for current financial year

CY calculation

=rangesum(above(SUM({<Particulars = {'Sales'},DataFlag= {1},[Strategic Code]={'C4'},ID_Mon = {$(=vIDMon)},[Time Span]={'Actual'},FiscalYear={"$(=max(FiscalYear))"},Description = {'Units sold KAM (Units)','Units sold KAM'},CalMonth=>}Data),0,vIDCount)).

But issue is while using rangesum function it is showing preceding month data in coming next month.

It should not show data in June month

Kindly let me know how it can resolve.

Thanks in advance

Regards

Sumita Chawla

Re: YTD accumulation

Kushal_Chawda — Sun, 19 Jun 2016 16:31:38 GMT

what is vIDCount ?

Re: YTD accumulation

effinty2112 — Sun, 19 Jun 2016 16:45:45 GMT

Hello,

I think the easiest way to achieve what you want is to add a YTD field in your load script.

Something like:

if(DayNumberOfYear(Date)<=DayNumberOfYear($(vToday)),1,0) as YTD

Now you can add a list box for YTD and if you select "1" any charts will give the YTD accumulations, or instead you can add this set modifier to expressions to make them show YTD values: {$<YTD = {1}>}

Cheers

Andrew

Re: YTD accumulation

Mon, 20 Jun 2016 05:58:07 GMT

In vIDcount I am calculating count of months for eg. in this case value of vIDcount is 2(Apr,May)

Re: YTD accumulation

Mon, 20 Jun 2016 06:04:18 GMT

Hello,

In Load script how would I calculate accumulation.

Please elaborate or provide example.

Re: YTD accumulation

sasikanth — Mon, 20 Jun 2016 06:12:59 GMT

hi,

Try this

Load *,

RangeSum(Sales,Peek('NewSales')) as NewSales

From Table;

Re: YTD accumulation

Kushal_Chawda — Mon, 20 Jun 2016 07:33:47 GMT

I think you need to restrict your forecast expression to previous 2 months, as Forecast expression is calculated for all the months, CY accum also performed for Jun month

Re: YTD accumulation

Mon, 20 Jun 2016 10:15:56 GMT

No I cant restrict forecast because I need to show forecast for current and next two months.

Re: YTD accumulation

Anonymous — Mon, 20 Jun 2016 12:25:04 GMT

Hi,

Please find the attachment, hope this solves your problem.

Re: YTD accumulation

Mon, 20 Jun 2016 16:12:09 GMT

Thanks Payal its working