Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
My financial periods are of the form mmyyyy, so 112104, 122014, 012015, 022015, etc.
I would like to do a set analysis to say "Show me the actuals if the period is in the current year"
I tried the following and it doesn't work;
Sum({$<NUM(Right(Period,4))={$(=NUM(YEAR(Today())))}>}[Actuals (YTD)])
Before I carry on hammering away at this, I just wanted to check whether this was even allowed, or whether I have to use an IF() statement in there instead so instead of NUM(Right(Period,4)) I have to use
Sum({$< IF (NUM(Right(Period,4)) = NUM(Year(Today()))) , Period , Null()) = {$(=NUM(YEAR(Today())))}>}[Actuals (YTD)])
(i.e. the Set Analysis must compare Period with a value - can it compare a function of Period with a value?
Or am I over-complicating it?
May be extract year in the script:
LOAD *,
Year(Period) as Year;
LOAD Date#(Period, 'MMYYYY') as Period
AllOtherFields
FROM ....
and then this expression:
Sum({$<Year={$(=Year(Today()))}>}[Actuals (YTD)])
I think you may be correct (and it would certainly make the expressions easier to read).
(Oh well, I can go and have a coffee while I wait for another load process! )
Hello,
As far as I see there is an easier way. Instead of using IF expressions, you can use set analysis to compare a value against current year:
Expression for current year:- Sum({<YearField = {'$(=Year(Today()))'}>} sales)
Expression for previous year:- Sum({<YearField = {'$(=Year(Today())-1)'}>} sales)
I hope this helps. Please mark this as helpful and correct if this does helps.