Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

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

Re: Comparison Operator in Set Analysis?

May be extract year in the script:


          Year(Period) as Year;

LOAD Date#(Period, 'MMYYYY') as Period


FROM ....

and then this expression:

Sum({$<Year={$(=Year(Today()))}>}[Actuals (YTD)])

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

Contributor III

Re: Comparison Operator in Set Analysis?


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