topic Re: Expression based on another expression value in QlikView

Expression based on another expression value

Fri, 29 Jan 2016 13:33:29 GMT

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!

Re: Expression based on another expression value

sunny_talwar — Fri, 29 Jan 2016 17:16:29 GMT

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

Gysbert_Wassenaar — Fri, 29 Jan 2016 17:17:47 GMT

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)

Re: Expression based on another expression value

Colin-Albert — Fri, 29 Jan 2016 17:19:48 GMT

You can reference another expression in the expression editor.

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

Re: Expression based on another expression value

Oleg_Troyansky — Fri, 29 Jan 2016 17:32:21 GMT

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!