Solved: Re: YTD - Qlik Community

NavinReddy · ‎2014-06-13

Dear All,

I am calculating ytd but im not getting any error in expression,but not working could some one help me

to do this

=sum({<Month={'$(vYearStart)'}>}Actuals) ------>This is working

=sum({<Month={'>=$(vYearStart)<=$(vCurrentMonth)'}>}Actuals) ----------->ITs Not working YTD

Please Help some one

Thanks In Advance

Niranjan

Anonymous · ‎2014-06-16

Hi Niranjan,

Create one tab at last and add below Coding...

YTD Exression:

=Num(Sum({$<MonthID = {"<=$(=Max(MonthID))"},Year= {$(=Max(Year))},Quarter = ,Month = >} Actuals),0.00)

Budgets:

Load

//Month,

Makedate(2014,NUM(MONTH(vCurrentMonth)),1) as Date,

Budget Resident Budget;

// Drop Butget Sales

DROP table Budget;

Master Calender script:

MinMax:

LOAD

MIN(Date) AS MinDate,

MAX(Date) AS MaxDate

RESIDENT Budgets;

LET vMinDate = NUM(PEEK('MinDate',0,'MinMax'));

LET vMaxDate = NUM(PEEK('MaxDate',0,'MinMax'));

LET vToday = $(vMaxDate);

//*****Temp Calendar*****

TempCal:

LOAD

DATE($(vMinDate) + ROWNO() -1) AS TempDate

AUTOGENERATE

$(vMaxDate) - $(vMinDate) + 1;

DROP TABLE MinMax;

//*****Master Calendar*****

MasterCalendar:

LOAD

TempDate AS InvoiceDate,

WEEK(TempDate) AS Week,

YEAR(TempDate) AS Year,

MONTH(TempDate) AS CMonth,

DAY(TempDate) AS Day,

WEEKDAY(TempDate) AS WeekDay,

AutoNumber(YEAR(TempDate) & MONTH(TempDate), 'MonthID') as [MonthID],

'Q' & If(CEIL(MONTH(TempDate))>=8 AND MONTH(TempDate)<=10 ,1,If(CEIL(MONTH(TempDate))>=2 AND MONTH(TempDate)<=4 ,3,If(CEIL(MONTH(TempDate))>=5 AND MONTH(TempDate)<=7 ,4,2))) AS Quarter,

date(yearstart(TempDate,1,8),'YYYY') &' - '& 'Q' & If(CEIL(MONTH(TempDate))>=8 AND MONTH(TempDate)<=10 ,1,If(CEIL(MONTH(TempDate))>=2 AND MONTH(TempDate)<=4 ,3,If(CEIL(MONTH(TempDate))>=5 AND MONTH(TempDate)<=7 ,4,2))) as YearQuarter,

AutoNumber(date(yearstart(TempDate,1,8),'YYYY') &' - '& 'Q' & If(CEIL(MONTH(TempDate))>=8 AND MONTH(TempDate)<=10 ,1,If(CEIL(MONTH(TempDate))>=2 AND MONTH(TempDate)<=4 ,3,If(CEIL(MONTH(TempDate))>=5 AND MONTH(TempDate)<=7 ,4,2))),'QuarterID') as QuarterID,

DATE(MONTHSTART(TempDate), 'MMM-YYYY') AS MonthYear,

WEEK(TempDate) & '-' & YEAR(TempDate) AS WeekYear,

date(yearstart(TempDate,1,8),'YYYY') AS YearFiscal,

//INYEARTODATE(TempDate,$(vToday),0,8) * -1 AS CurYTDFlag,

//INYEARTODATE(TempDate,$(vToday),-1,1) * -1 AS LastYTDFlag

INYEARTODATE(TempDate,$(vToday),0,8) * -1 AS CurYTDFlag,

INYEARTODATE(TempDate,$(vToday),-1,8) * -1 AS LastYTDFlag

//INYEARTODATE(TempDate,TempDate,0,8) * -1 AS CurYTDFlag,

//INYEARTODATE(TempDate,TempDate,-1,8) * -1 AS LastYTDFlag

RESIDENT TempCal

ORDER BY TempDate ASC;

DROP TABLE TempCal;

View solution in original post

its_anandrjs · ‎2014-06-13

Try this way

Let vYearStart = Max( YearStart(Datefield) );

Let vCurrentMonth = Max( Today() );

Or

Let vCurrentMonth = Max(YourDateField);

And in expression like this way

=Sum({$<Datefield = {'>$(vYearStart) <=$(vCurrentMonth)'}>} Sales)

In place of the Datefield use YourDatefield in place of the Month.

swuehl · ‎2014-06-13

How have you defined your variables, what is the current value of these variables and what is the format of your field Month?

It would be easiest if you could post a small sample app.

NavinReddy · ‎2014-06-13

Hi swuehl,

my variables

vYearStart=date(YearStart(Today()),'MMMM')

This is ths Month field format i using

Best regards,

Niranjan

NavinReddy · ‎2014-06-13

Let vYearStart=date(YearStart(Today()),'MMMM')

Like this format im using

NavinReddy · ‎2014-06-13

Hi anand chouhan,

Please find the attechment

Middle chart im not getting the values

Please help me

Best Regards,

Niranjan

swuehl · ‎2014-06-13

Your Month field is a pure text field, not having dates with a numeric representation.

I think that could be the issue here.

Besides that, I would consider re-modeling your three fact tables into a concatenated fact table.

NavinReddy · ‎2014-06-13

Hi Swuehl

Please find the attechment,Month filed format everything should i mention

Middle chart im not getting the values

Please help me

Best Regards,

Niranjan

NavinReddy · ‎2014-06-13

Guys any Ideas

swuehl · ‎2014-06-14

Niru Niru,

yes, I have some ideas and tried to express above.

1a) Use the same format for your field Month and the search variables. Currently, they differ

1b) Consider using a Month field using dual values as retrieved by using QV Month() function or using dual() function, instead of the pure text values if you want to show month values (i.e. independend from year).

Consider using a MonthStart if you want to show month values, but depending on year, so Year-Month values in fact.

From your current approach, it's not clear to me what you want to achieve with the Month field and your set expression.

2) Consider using a concatenated fact table instead of three tables linked by a syn key.

http://qlikviewnotes.blogspot.de/2009/11/understanding-join-and-concatenate.html

Regards,

Stefan