topic Re: Create YTD in Script in QlikView

Create YTD in Script

qliktoview123 — Wed, 25 Nov 2020 16:16:04 GMT

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.

Re: Create YTD in Script

Anonymous — Mon, 25 Jul 2016 11:39:47 GMT

Hi,

This may be solve your problem,

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

Re: Create YTD in Script

Digvijay_Singh — Mon, 25 Jul 2016 11:48:05 GMT

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;

Re: Create YTD in Script

rubenmarin — Mon, 25 Jul 2016 12:16:01 GMT

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;

Re: Create YTD in Script

vinieme12 — Mon, 25 Jul 2016 14:53:55 GMT

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