15 Replies Latest reply: Jul 9, 2018 5:32 AM by Igor Banjac

# YTD, MTD issue

Hi,

Having a bit of trouble creating a YTD/MTD measure in qlik sense.

I have read the below and it's not making a lot of sense to me unfortunately.

YTQ, QTD, MTD and WTD

My Date Field is called DATE in the table. So I have entered Floor(DATE) as DateNum in my script

I've also created

LET vToDate = Today();

LET vToDatePrevious = Today()-365;

However when I add the last formula for the above discussion to my measure it does not filter YTD but shows the total overall for all years. See formula below, may be long winded but I'm very new to this.

Sum(    {<DELETED_FLAG={'0'}, TYPE = {'SI' , 'SC'} , NOMINAL_CODE = {'4000', '4101' , '4100' , '4002' , '4003' , '4102'} ,Year=, Month=, Quarter=, Week=, DATE=, DateNum={">=\$(=Num(\$(=vToDate& 'Start(Max(DateNum))')))<=\$(=Max(DateNum))"}  >}    NET_AMOUNT)

Can anyone point out my error please?

Thanks,

Nick

• ###### Re: YTD, MTD issue

Hi Nicholas,

Don't panic, set expressions seems difficult at first sight, but then you'll love working with them.

Let's suppose you have a date field (you don't have to create variables); only a date field will do the job.

And let's suppose you've created a calendar in your script so you have Year,Month, Quarter and Week fields

YTD: if we select 12/04/2016: YTD will return our measure from 01/01/2016 to 12/04/2016

How we do that?

Suppose our measure is : sum(Sales)

1)First changes: sum({<date=,Year=,Month=,Quarter=>}Sales)

We add these to force Qlik to not take into consideration our selection of date for example.

Let me explain in better words, if you don't write the "date=" and select the date 12/04/2016; Qlik will filter all the data to that selected point and then return the sum(Sales) for the day 12/04/2016.

To prohibit this, we must write the date=.

2) Second change: sum({<date=,Year=,Month=,Quarter=, date={">=\$(=YearStart(Max(date)))<=\$(=Max(date))"}>}Sales)

Let explain this : date={">=\$(=YearStart(Max(date)))<=\$(=Max(date))"}

We want to work from date=01/01/2016 to the selected date=12/04/2016 right?

So we're working with the field :

a) date={    }

b) Now we wanna this date to be <=selected date which is max(date) ;

max(date) is a function so it needs an "=" sign:

=max(date)

when we have a '=' we add the \$ (before each calculation) : \$(=max(date) ) => this is 12/04/2016

Now we add the <= so we'll have :  <=\$(=max(date) )

for the second part, we want our date to be >=01/01/2016 which is the start of the year:

a) same approach, we use the YearStart function that returns the start of the selected year: >=\$(=YearStart(Max(date)))

Now our expression is : from : date={    }

to : date={>=\$(=YearStart(Max(date)))<=\$(=Max(date))}

Now, in a set expression, if we wanna work with the year 2016 for example which is numeric: we call it without quotes:

Year={2016}

If we wanna focus on a country, Tunisia for example which is a string: we call it with quotes

Country={'Tunisia'}

In our case, we focusing on a range of dates created by an expression, so we surround it by double quotes:

date={">=\$(=YearStart(Max(date)))<=\$(=Max(date))"}

Final expression for YTD:

sum({<date=,Year=,Month=,Quarter=, date={">=\$(=YearStart(Max(date)))<=\$(=Max(date))"}>}Sales)

Same approach for MTD:

sum({<date=,Year=,Month=,Quarter=, date={">=\$(=MonthStart(Max(date)))<=\$(=Max(date))"}>}Sales)

Hope this helps,

Omar,

• ###### Re: YTD, MTD issue

Hi Omar,

Thanks for the above, however I still can't seem to get it working. I have a calculation for full year 2017 so therefore if I use the above and select the same date I should in theory end up with the same number? This isn't the case for me.

My current formula is now:

Sum(    {<DELETED_FLAG={'0'}, TYPE = {'SI' , 'SC'} , NOMINAL_CODE = {'4000', '4101' , '4100' , '4002' , '4003' , '4102'} ,DATE=,Year=,Month=,Quarter=,DATE={">=\$(YearStart(Max(DATE)))<=\$(=Max(DATE))"}  >}    NET_AMOUNT)

Also is there a way to make this function dynamic so it is always looking at today's date YTD? Without having to actually select a date?

Thanks,

Nick

• ###### Re: YTD, MTD issue

In your current expression, you have this :

DELETED_FLAG={'0'}, TYPE = {'SI' , 'SC'} , NOMINAL_CODE = {'4000', '4101' , '4100' , '4002' , '4003' , '4102'}

That means that you're only returning the Net Amount 'YTD' but also and only for the deleted Flag =0, for TYPE SI and SC and for the above nominal codes.

I think your second expression (for the full year) does not contain this first part?

for your second question, we're already forcing Qlik to take into consideration the max(date) that exists in your model.

If you select a date, the selected date will be the max(date); if you do not select a date, Qlik will look for the max(date) in your source and work with.

Now if you want it to ALWAYS return YTD for the max existing date no matter what selection you make,

you alter max(DATE) to max({1}DATE) which means telling QLIK to always return the max date no matter what selection we make.

If sthing is not clear, don't hesitate to poke me

• ###### Re: YTD, MTD issue

Hi Omar,

Thanks for coming back to me. My full year expression is basically the same and is as follows

Sum(    {<DELETED_FLAG={'0'}, TYPE = {'SI' , 'SC'} , NOMINAL_CODE = {'4000', '4101' , '4100' , '4002' , '4003' , '4102'} ,Year ={'\$(vYear_Current)'}  >}    NET_AMOUNT)

Just for some reason I can't get that YTD function to work.

• ###### Re: YTD, MTD issue

Can you please attach your QVF file?

• ###### Re: YTD, MTD issue

Hi Omar,

My App contains sensitive data, so I will try and create one with some mock data in the same format, or just the fields I am using to send over to you!

Thanks,

Nick

• ###### Re: YTD, MTD issue

Hi Omar,

Please see dummy app attached.

Thanks again for the assistance!

Nick

• ###### Re: YTD, MTD issue

Hi Nich,

when you create a DATE={"EXPRESSION"} as in our case, please don't leave BLANKS:

DATE={">=  \$( =YearStart  (Max(DATE)))<=  \$(=Max(DATE))    "}

Because we're surronding the expression by " ", when we leave blanks, Qlik will misinterpret our expression:

DATE={">=\$(=YearStart(Max(DATE)))<=\$(=Max(DATE))"}

Find your app attached, it's all working just fine

If your question ahs been answered, please close the thread but marking the correct answer as so !

Have a nice day

• ###### Re: YTD, MTD issue

Hi Omar,

Amazing, thanks for your time on this!!

Out of interest how easy is it to then edit this expression to get previous YTD/MTD for comparisons?

Nick

• ###### Re: YTD, MTD issue

Hi again Nich,

Sorry for being late, I was taking my lunch !

So, if we wanna focus on the YTD-1, we wanna alter this part:

DATE={">=\$(=YearStart(Max(DATE)))<=\$(=Max(DATE))"}

to do so, we want to situate our self in the previous year, to do that, their is a function called addYears.

How we use it? addYears('04/12/2017',-1) = 04/12/2016;

So our expression will become:

With this, if the max(Date) in 2017 is 24/03/2017 (like in your case)

The YTD-1 will return the NET_AMOUNT from 01/01/2016 to 24/03/2016 .

See the attached app

You'll find YTD, YTD-1

MTD, MTD-1

Your max(DATE) = 24/03/2017

Create a filter date, try to change your date and see how your expression will change to become from the year/month start to the selected date.

As of your expression using vCurrentYear, when you select a date, it wil return the Net Amount of only the selected date

• ###### Re: YTD, MTD issue

Hello omarbensalem can you help me with my case?

=sum({\$<Date_Year={'\$(=year(vMaxDateSalles)-1)'}, Date_Month={"<\$(=month(vMaxDateSalles))"}>} s_Salles)

vMaxDateSalles is a variable I created with the max Date.

Am I doing something wrong? I tried with ' instead of ", and also tried <= instead of <  but 0 success.

Miguel Cunha

• ###### Re: YTD, MTD issue

How is your variable defined?

Can you copy it?

• ###### Re: YTD, MTD issue

Its defined like this:

=Max(Date_Date)

Date_Date is the full date in my Date Dimension

• ###### Re: YTD, MTD issue

does this work?

=sum({\$<Date_Year={'\$(=year(Max(Date_Date))-1)'}, Date_Month={"<\$(=month(Max(Date_Date)))"}>} s_Salles)

• ###### Re: YTD, MTD issue

Hi Omar, I see that you have both the knowledge and willingness to help, which is really awesome. I have a small problem and I was wandering if you could help me.

For selected year total sales KPI I created this expression:

Sum({\$<OrderDate.autoCalendar={Year}>}Sales)

Now I'm having trouble doing it for the previous year total sales KPI that I want to put next to the first one.

How do I do it?