Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi
So I have a table showing MTD sales, and YTD Sales.
I have a filter pane where I can select the month and/or the year.
The MTD expression is RangeSum(Sum({<Year=, Month=, Quarter=, Week=, DateField=, TRANS_DATE={">=$(=Num(MonthStart(Max(TRANS_DATE))))<=$(=Max(TRANS_DATE))"}>} QTY))
THE YTD expression is Sum({<Year=, Month =, Quarter=, Week=, DateField=, TRANS_DATE={">=$(=Num(YearStart(Max(TRANS_DATE))))<=$(=Max(TRANS_DATE))"}>} QTY)
The issue I have is this: with a month selected, the MTD and YTD figures are exactly the same (they are the correct MTD figures).
With nothing, or just a year, selected, there is no longer a MTD (which is probably logical) but the YTD is correct.
I simply cannot work out how to get it to show the correct MTD (as of the month selected) and YTD on the same table.
I don't necessarily mind if the YTD doesn't reflect the "as of" the month selected - it can be YTD as of latest data regardless of month selected if need be. It would be nice if it did, but not a show-stopper.
Any help greatly appreciated, thanks.
hi
why do you use
the num function in your expression ?
I've only been working with QlikSense (and SQL) for 3 weeks now - and I have taken over maintaining/expanding reports that someone else built.
So something I've learned very quickly is that it is a right royal pain in the you-know-where trying to decipher what someone has written, and the reasons they did it, when you yourself only have a very basic understanding of the language.
In short - I have no idea sorry.
What is the format of values in TRANS_DATE?
YYYY-MM-DD