Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Not applicable

Expression based on another expression value

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!

4 Replies

Re: Expression based on another expression value

May be something like this:

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

Re: Expression based on another expression value

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)


talk is cheap, supply exceeds demand

Re: Expression based on another expression value

You can reference another expression in the expression editor.

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

MVP
MVP

Re: Expression based on another expression value

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!

Community Browser