topic Re: Re: YTD in QlikView

YTD

NavinReddy — Fri, 13 Jun 2014 18:41:56 GMT

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

Re: YTD

its_anandrjs — Fri, 13 Jun 2014 18:50:34 GMT

Try this way

Let vYearStart = Max( YearStart(Datefield) );

Let vCurrentMonth = Max( Today() );

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.

Re: YTD

swuehl — Fri, 13 Jun 2014 18:51:04 GMT

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.

Re: YTD

NavinReddy — Fri, 13 Jun 2014 18:57:45 GMT

Hi swuehl,

my variables

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

This is ths Month field format i using

Best regards,

Niranjan

Re: YTD

NavinReddy — Fri, 13 Jun 2014 18:59:56 GMT

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

Like this format im using

Re: YTD

NavinReddy — Fri, 13 Jun 2014 19:17:56 GMT

Hi anand chouhan,

Please find the attechment

Middle chart im not getting the values

Please help me

Best Regards,

Niranjan

Re: YTD

swuehl — Fri, 13 Jun 2014 19:52:31 GMT

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.

Re: YTD

NavinReddy — Fri, 13 Jun 2014 19:59:13 GMT

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

Re: YTD

NavinReddy — Fri, 13 Jun 2014 21:46:58 GMT

Guys any Ideas

Re: YTD

swuehl — Sat, 14 Jun 2014 10:05:19 GMT

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

Re: YTD

its_anandrjs — Sat, 14 Jun 2014 10:43:28 GMT

You have to use Datefield inplace of the Month field that will be better.

Re: YTD

NavinReddy — Mon, 16 Jun 2014 09:45:56 GMT

Hi anand chouhan,

sorry dont know how to solve this i attached sample app

please check and any suggessions

Best Regards,

Niranjan

Re: YTD

Anonymous — Mon, 16 Jun 2014 10:03:12 GMT

Try this expression.

=sum({<Month={">=$(vYearStart)<=$(vCurrentMonth)"}>}Actuals)

Regds.

Raju Kuppusamy

Re: YTD

NavinReddy — Mon, 16 Jun 2014 10:12:33 GMT

Hi Raju,

No the same error again result i m getting

Thanks for reply

Best Regards,
Niranjan

Re: YTD

Anonymous — Mon, 16 Jun 2014 10:38:16 GMT

Hi Niranjan,

Your variables are representing text values.

First expression is working case conditions are just like where condition but in second expression you have been using

<=,>= conditions. You can use this conditions in only numeric values.

Assign IDs for each month and apply this condition then it will work.

Regards,

Raju Kuppusamy

Re: YTD

NavinReddy — Mon, 16 Jun 2014 10:56:17 GMT

Hi raju,

How can i do this ,

you mean to say converting into nummeric format

if you have any idea i attached sample app ,

please give me example its better to me understand

Best Regards,

Niranjan

Re: Re: YTD

Mon, 16 Jun 2014 11:42:59 GMT

Hi,

PFA

use month number instead of Month,

Hope it ll help....

Re: YTD

NavinReddy — Mon, 16 Jun 2014 12:27:03 GMT

Hi Saranya Arumugam,

Thanks for your reply

its giving full year values

its not filtering the YTD please check im not able to figure out this

Best Regards,

Niranjan

Re: YTD

Anonymous — Mon, 16 Jun 2014 13:31:09 GMT

Hi Nirajan,

Use Makedate() function to create date field in fact table. then use master calender to format your months, year, qtr .. etc..

Once you added then write set expression coding to calculate YTD values.

Ex YTD Expression:

=Num(Sum({$<MonthID = {"<=$(=Max(MonthID))"},YearFiscal = {$(=Max(YearFiscal))},Quarter = ,Month = >} #SalesMT),0.00)

Below is the master coding:

MinMax:

LOAD

MIN(InvoiceDate) AS MinDate,

MAX(InvoiceDate) AS MaxDate

RESIDENT Facts;

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 Month,

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;

Regards,

Raju Kuppusamy

Re: YTD

NavinReddy — Mon, 16 Jun 2014 13:44:42 GMT

Hi Raju,

Thanks for your efforts,thank you so much spending your valuble time

But when i m loading that code im getting table not found,script error these errors i am getting

please provide if you done previous any QVW its better to under stand to me

The code giving 3 errors

Best Regards,

Niranjan