YTD, MTD issue

[#1]

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

[OmarBenSalem]

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:

DATE={">=$(=YearStart(addYears(max(DATE),-1)))<=$(=addYears(max(DATE),-1))"}

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

[Anonymous]

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.

Thanks in advance,
Miguel Cunha

[OmarBenSalem]

How is your variable defined?

Can you copy it?

[Anonymous]

Its defined like this:

=Max(Date_Date)

Date_Date is the full date in my Date Dimension

[OmarBenSalem]

does this work?

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

[Anonymous]

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?

Thanks in advance.

[sangeeth8787]

Hi Omar,

I am really thankful for your detailed explanation.  I got very positive feeling on Set Analysis now.

[LuisM]

Hola, 

Quiero saber si es posible que al filtrar mi AÑO y MES, me de los datos YTD y LYTD para mi calculo?

Es decir, si filtro por el mes de Año 2022 y Mes Mayo, que también me filtre 2021 Mayo

Gracias, soy nuevo usando Qlik