Qlik Community

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
WE ARE LISTENING! New Navigation for Qlik Community, Sept. 26: TELL ME MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

How do I create a Year to Date Measure?

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.

qliksense example.png

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?

1 Solution

Accepted Solutions
Not applicable
Author

Hi Vignesh,

Turns out I need to use the rangesum function to achieve what I wanted. The following expression works for me.

rangesum(above(SUM([Month Actual]), 0, [Fiscal Month]))

I should have been more clear in what I wanted.

Thanks for the help anyway!

View solution in original post

10 Replies
vigneshvs
Creator II
Creator II

Hi,

These kind of analysis mainly depends on the date formats.

So request you to post sample qvw

Thanks,

Vignesh

Not applicable
Author

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!

Not applicable
Author

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

smartwin18
Partner - Contributor II
Partner - Contributor II

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

Not applicable
Author

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
Partner - Contributor II
Partner - Contributor II

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;

Not applicable
Author

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

Not applicable
Author

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

qliksense example.png

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?

vigneshvs
Creator II
Creator II

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