Create YTD in Script

[qliktoview123]

hello,

How can we calculate YTD in script.I have Year,Month and sales and need to calculated the YTD logic in backend.

Please help out.

[Anonymous]

Hi,

This may be solve your problem,

LET YTD = '>='&YearStart(today())&'<='&today();

[Digvijay_Singh]

You can use YTD function and create flagfs as below, check this functions in help in case you want to shift default month etc.

-Year2Date(OrderDate) as Order_YTD_Flag,

  -Year2Date(OrderDate,-1) as Order_LYTD_Flag;

[rubenmarin]

Hi Atul, maybe this script helps:

LOAD Year,

     Month,

     Sales,

     If(Peek('Year')=Year, RangeSum(Peek('SalesYTD'), Sales), Sales) as SalesYTD

Resident ...

OrderBy Year, Month;

[vinieme12]

This will allow you to have YTD figures for each year available

FACT:

LOAD * INLINE [

Year,Month,Sales

2013,1,113

2013,2,1

2013,3,180

2013,4,283

2013,5,221

2013,6,165

2013,7,230

2013,8,267

2013,9,142

2013,10,324

2013,11,69

2013,12,257

2014,1,443

2014,2,51

2014,3,399

2014,4,243

2014,5,252

2014,6,487

2014,7,384

2014,8,96

2014,9,266

2014,10,189

2014,11,234

2014,12,184

2015,1,39

2015,2,128

2015,3,288

2015,4,450

2015,5,493

2015,6,104

2015,7,146

2015,8,446

2015,9,289

2015,10,69

2015,11,242

2015,12,17

2016,1,19

2016,2,384

2016,3,388

2016,4,336

2016,5,188

2016,6,454

2016,7,433

2016,8,210

2016,9,356

2016,10,283

2016,11,241

2016,12,219

];

let vCurrMonth = num(month(Today())) ;

MIN_MAX:

LOAD

  min(Year) as MinYear,

  max(Year) as MaxYear

RESIDENT FACT;

let vMinYear = peek('MinYear',0,'MIN_MAX');

let vMaxYear = peek('MaxYear',0,'MIN_MAX');

FOR i = $(vMinYear) to $(vMaxYear)

YTDSALES:

  LOAD

  Year,

  Sum(Sales) as SalesYTD

  RESIDENT FACT

  Where Month <= $(vCurrMonth)

  Group BY Year;

Next i