Expression based on another expression value

[#1]

Hi all,

I have the following 2 expressions:

Total Visits
=SUM( <{Store=, Location='Location A'}> Visits)

Total Sales
=SUM( <{Store=, Location='Location A'}> Sales)

Is it possible to create an expression based on these 2 for: Total Sales where total number of visits >= 3 ?

I need to incorporate the total of one expression in another expression, but I do not know how to achieve this...

Thanks

Jon!

[sunny_talwar]

May be something like this:

If(Sum({<Store=, Location={'Location A'}>} Visits) >=3, Sum({<Store=, Location={'Location A'}>} Sales)

[Gysbert_Wassenaar]

What kind of thing/person/animal is having this number of visits? What dimension or dimensions is the visits sum calculated over?

This will calculate the sales for the Elephants in Location A that had 3 or more visits while present in Location A:

Sum({<Elephant={"=sum({<Store,Location={'Location A'}>}Visits)>=3"},Store,Location={'Location A'}>} Sales)

[Colin-Albert]

You can reference another expression in the expression editor.

if( [Total Visits] >= 3, [Total Visits] )

[Oleg_Troyansky]

If you literally want to condition the result in one expression by the total number displayed in the other expression, then Colin's formula gives you the desired result.

However, if you wanted to summarize sales in those Stores where the total number of visits was > 3, and it's not necessarily the overall total of the first expression, then you need to use Set Analysis with Advanced Search:

=SUM( <{Store={"=SUM( <{Store=, Location='Location A'}> Visits)>=3"}, Location='Location A'}> Sales)

Cheers,

Oleg Troyansky

Upgrade your Qlik skills at the Masters Summit for Qlik - coming soon to Milan, Italy!