Skip to main content
Announcements
See what Drew Clarke has to say about the Qlik Talend Cloud launch! READ THE BLOG
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 ()".