Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
ziabobaz
Creator III
Creator III

Reference to the measure itself in the formula (without IF)

Hi,

Let's say I want to calculate a measure SUM( Sales ), but I want to show only the numbers above certain threshold (the others should return Null).

I can do it using if:

if( SUM (Sales) < 100, SUM (Sales), Null() )

Is there way to do the same without using IF, like using Set Analysis?

Smth like: SUM (   {< SUM(Sales)<{100} >}    [Sales] )

Thank you!

Labels (1)
  • SaaS

1 Solution

Accepted Solutions
Not applicable

Hi,

the equivalent would be

sum({$<sales={"<50"}>}sales)

I cannot take all the credit here i read this guide which i found really helpfull

A Primer on Set Analysis

View solution in original post

6 Replies
sanjay006
Creator
Creator

Hi Aitov


Try This


sum({<Sales={">=0<100"}>}Sales)

Not applicable

Hi,

the equivalent would be

sum({$<sales={"<50"}>}sales)

I cannot take all the credit here i read this guide which i found really helpfull

A Primer on Set Analysis

sanjay006
Creator
Creator

Dear Aitov

For that u need to take the Sum(Sale) field from back end using Aggr() function.

Not applicable

replace the 50 with 100 little typo there

paolo_mapelli
Creator II
Creator II

Personally I consider this feature one of the most missing one in QS, that would be very useful in lot of situations, like this:

Clipboard02.jpg

Now (if you don't want to create a master measure expression, of course) you have to copy and paste measure definition in those functions definitions, maybe multiple times for complex ones, when you would just have a kind of "self-reference" to measure itself. E.g: if(this()<100,"do this 1",if(this()<200,"do this 2",if(this()<300,"do this 3","do that")))

Moreover above example shows the other most missing feature of QS, for me: the switch() statement (furthermore known as "case"), e.g.:

case this()

when <100 then,"do this 1"

when <200 then "do this 2"

when <300 then "do this 3"

else "do that"

end

 

Arthur_Marseuti
Partner - Contributor II
Partner - Contributor II

That would really be awesome to reference within the colour function field to the measure function with somthing like "this ()".