Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

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?

Tags (2)
1 Solution

Accepted Solutions
Not applicable

Re: How do I create a Year to Date Measure?

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!

10 Replies
vigneshvs
Contributor II

Re: How do I create a Year to Date Measure?

Hi,

These kind of analysis mainly depends on the date formats.

So request you to post sample qvw

Thanks,

Vignesh

Not applicable

Re: How do I create a Year to Date Measure?

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

Re: How do I create a Year to Date Measure?

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

smartwin18
New Contributor

Re: How do I create a Year to Date Measure?

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

Not applicable

Re: How do I create a Year to Date Measure?

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
New Contributor

Re: How do I create a Year to Date Measure?

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

Re: How do I create a Year to Date Measure?

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

Re: How do I create a Year to Date Measure?

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

Re: How do I create a Year to Date Measure?

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