How do I create a Year to Date Measure?

[#1]

Hi guys,

I'm trying to make a couple of Year To Date measures (Month Target & Month Actual) but I'm not sure how to do this. The YTD should be starting from July in the year selected.

For years I use the field 'Financial Year', for months I use 'Period' (starts in July), and the measures are 'Month Target' and 'Month Actual'.

There is another field 'MonthYear' which I am able to use if it's easier eg. August in 2012/13 is 'Aug-12', while August in 2013/14 is 'Aug-13'

If anyone is able to help me solve this that would be amazing!

Thanks in advance.

Edit:

To further explain, I basically want to do what I can do on Excel, but in Qlik Sense.

So I'm able to select 2013/14, and then select November, and the amount showing is 670.

Does anyone know how I'm able to do this?

[Anonymous]

Hi,

These kind of analysis mainly depends on the date formats.

So request you to post sample qvw

Thanks,

Vignesh

[#3]

Hey sorry for late reply I was trying to install 1.1 before sorting out this issue.

Please see attatched for the MasterCalendar.

Thanks in advance if you guys are able to help!

[#4]

Hi Vignesh. I just attached the MasterCalendar if that's useful

[smartwin18]

easier way would be to add a YTD flag, and mark all those date entries which qualify as YTD.

[#6]

Hi Ashwin,

I'm not too sure how to do that, I didn't create the calender and my knowledge of syntax is next to nothing.

Would I basically copy the formula for MTDFlag and change 'monthstart' to 'yearstart'?

Edit:

I've just discovered that 'CurFiscalFlag' acts as a FiscalYTDFlag, but I'm still not able to calculate the YTD for my amounts.

Is there a way I can use CurFiscalFlag to Sum the amounts?

[smartwin18]

Hmm, right.. you could do something like that.. see below example

\\Facts is the transaction data table ..., check the Function InYearToDate, you can change parameters to suit your needs.

MinMax:
Load
Min(Date) as MinDate,
Max(Date) as MaxDate
Resident Facts;

LET vMinDate = Peek('MinDate',0,MinMax);
LET vMaxDate = Peek('MaxDate',0,MinMax);
LET vToday = $(vMaxDate);

TempCalendar:
Load
date($(vMinDate) + Rowno() -1)As TempDate
AutoGenerate $(vMaxDate)-$(vMinDate)+1;

MasterCalendar:
Load
TempDate as Date,
Year(TempDate) as Year,
month(TempDate) as Month,
day(TempDate) as Day,
week(TempDate) as Week,
'Q'& Ceil(Month(TempDate)/3) as Quarter,
Date(MonthStart(TempDate), 'MMM-YYYY') as MonthYear,
week(TempDate) & Year(TempDate) as WeekYear,
InYearToDate(TempDate,$(vToday),0)*-1 As CurYTDFlag, // define as per ur need
InYearToDate(TempDate,$(vToday),-1)*-1 As LastYTDFlag // define as per ur need
Resident TempCalendar
Order by TempDate Asc;

Drop Tables MinMax,TempCalendar;

[#8]

Hi Ashwin,

Thanks for that! But I'm not too sure if it'll work as I'm working with financial year data (July to June).

Is there any expression of measure I can use to calculate the YTD for each year based on a selected month (eg. I select December so it totals up every value from July to December), rather than just fiilter the selection based on the current year?

Can someone please explain to me how to incorporate this expression into my app:

Sum({<Year=, Month=, Quarter=, Week=, DateField=, DateNum={">=$(=Num(YearStart(Max(DateNum))))<=$(=Max(DateNum))"}>} Sales)

http://community.qlik.com/docs/DOC-6163

[#9]

To further explain, I basically want to do what I can do on Excel, but in Qlik Sense.

So I'm able to select 2013/14, and then select November, and the amount showing is 670.

Does anyone know how I'm able to do this?

[Anonymous]

Hi James,

Fiscal Year

Please go through this to create a Fiscal year and use the formula for YTD as

Sum({<fYear={$(=max(fYear))}, Fyear= , fMonth={"<=$(=Max(fMonth))"} , FMonth= >} Amount)

Hope this helps.

Thanks,

Vignesh