Announcements
See what Drew Clarke has to say about the Qlik Talend Cloud launch! READ THE BLOG
cancel
Showing results for
Did you mean:
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

6 Replies
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

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

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:

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

Partner - Contributor II

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

Community Browser