Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Qlik® Product Spotlight: Discover what’s possible. Get more from our products.
See for yourself. Register today.
Not applicable

YTD in Qlik Sense

I am trying to get the YTD sum of Debit - Credit. This is based on [TRX Date].

I have tried the following but it's only returning 0.

sum(

     {$<

     [TRX Date]= {"<=$(=max(total([TRX Date])))>=$(=YearStart(date( max( total [TRX Date]),'DD/MM/YYYY'),0))"},

     >}

     (Debit-Credit)

     )

As a side note, I tried to modify the code from the answers to the following threads but neither one worked

Current YTD vs Previous year YTD | Qlik Community

YTD,QTD,MTD,WTD IN Qlik Sense

YTQ, QTD, MTD and WTD

Thanks!

Edit: I forgot to mention that the YTD should be based on the maximum selected [TRX Date] in the filter (and not based on Today())

Message was edited by: Amir Jaber

Tags (3)
8 Replies
jens_leysen
Not applicable

Re: YTD in Qlik Sense

Why these documents won't work is because they are made for scripts that run a Master Calendar and have all date parts seperated. I think you're making it way too hard for yourself. Try this:

Sum( {< Year([TRX Date]) = Year(Today()) >} Debit - Credit)

You just set the Year() value of [TRX DATE] to the current year, so everything in the current year will be selected.

Hope this works.

vinieme12
Not applicable

Re: YTD in Qlik Sense

why not add a YTD flag during load?

if(InYearToDate(TempDate,Today(),0) * -1)>0,1) as YTDFlag,

and use this flag in set analysis

sum(

     {$<

     YTDFlag = {1}

     >}

     (Debit-Credit)

     )

vinieme12
Not applicable

Re: YTD in Qlik Sense

Hi Jens


Using expressions on the left side of the operator is not possible.

karthiksrqv
Not applicable

Re: YTD in Qlik Sense

Hi,

If you are working with the Gregorian calendar, why not this?

sum(

     {$<[TRX Date]= {">=$(=MakeDate(year(now()),1,1))"}>}

     (Debit-Credit)

     )

jens_leysen
Not applicable

Re: YTD in Qlik Sense

You are right.

Amir, I would suggest you add Year([TRX Date]) as Year in your script and then you can do a simple set expressions based on your year. Also possible withs months etc.

With these fields you can create YTD functions easier and faster.

Hope this works.

vinieme12
Not applicable

Re: YTD in Qlik Sense

Not applicable

Re: YTD in Qlik Sense

Thanks, I will give this a go later

r_moreno
Not applicable

Re: YTD in Qlik Sense

This is a Good solution..Check this demo