New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
BI & Data Trends 2021. Discover the top 10 trends emerging in today. Join us on Dec. 8th REGISTER
cancel
Showing results for
Did you mean:
Highlighted
Not applicable

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

1 Solution

Accepted Solutions
Highlighted
Partner

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

Have a nice day

16 Replies
Highlighted
Partner

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,

Highlighted
Not applicable

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

Highlighted
Partner

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

Highlighted
Not applicable

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.

Highlighted
Partner

Highlighted
Not applicable

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

Highlighted
Not applicable

Hi Omar,

Thanks again for the assistance!

Nick

Highlighted
Partner

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