Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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.
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)
)
Hi Jens
Using expressions on the left side of the operator is not possible.
Hi,
If you are working with the Gregorian calendar, why not this?
sum(
{$<[TRX Date]= {">=$(=MakeDate(year(now()),1,1))"}>}
(Debit-Credit)
)
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.
if this helps
Period Presets: Compare Periods on the fly
Thanks, I will give this a go later
This is a Good solution..Check this demo