Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

quriouss
Contributor III

Comparison Operator in Set Analysis?

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?

3 Replies
MVP
MVP

Re: Comparison Operator in Set Analysis?

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)])

quriouss
Contributor III

Re: Comparison Operator in Set Analysis?

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! )

mjm
Contributor III

Re: Comparison Operator in Set Analysis?

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.

Community Browser