topic Re: Loyal customer per years using indirect set analysis in QlikView

Loyal customer per years using indirect set analysis

Anonymous — Mon, 26 Jan 2026 18:19:17 GMT

Hello,

I' m try to have a pivot table whit my loyal customer by year with the sum of amount by years.

my dimension are : Customer and Year

my expression is :

sum ( {<Customer=p({<Year={'<$(=max(Year))','<$(=max(Year)-1)'}>}Customer)>} sales)

The problem is when i select year 2014 the pivot table not show me also year 2013

See attchament

Thanks in advance for your help

Andrea

Re: Loyal customer per years using indirect set analysis

tresB — Thu, 27 Aug 2015 10:53:32 GMT

Year filter is not ignored in the main(outer) set expression, hence it shows only the selected year data. What are you actually trying to achieve? P() should be used if you wish to get all sales for the customers who have participated in 2014,2013. And mind it, if a customer A is there in sale either in 2015 or 2014 ... all it's sales even beyond 2014 would come up with p(). Do you want that?

Re: Loyal customer per years using indirect set analysis

Anonymous — Thu, 27 Aug 2015 11:00:17 GMT

I would like customer both selling in 2014 and 2013 divede by year like for exemple and ignore all other

customer that was selling or in 2014 and not in 2013 or 2013 but not in 2014

like this

customer 2014 2013

A1 100 20

B1 50 10

and let the user decide witch year want the comparate

Re: Loyal customer per years using indirect set analysis

tresB — Thu, 27 Aug 2015 11:29:22 GMT

So is it those customers who are common in both the years?

Re: Loyal customer per years using indirect set analysis

Anonymous — Thu, 27 Aug 2015 11:39:10 GMT

In the exemple customer common in 2014 and 2013 are : A1 , B1 , E1

Re: Loyal customer per years using indirect set analysis

jonathandienst — Thu, 27 Aug 2015 11:51:42 GMT

How about this expression:

Sum({<Year = {$(=max(Year))}> *

<Customer = P({<Year = {$(=max(Year))}>} Customer)> *

<Customer = P({<Year = {$(=max(Year)-1)}>} Customer)>}

sales)

Re: Loyal customer per years using indirect set analysis

tresB — Thu, 27 Aug 2015 11:52:19 GMT

Like this?

Sum({<Year={'<=$(=Max(Year))>=$(=Max(Year)-1)'},Customer=p({<Year={'$(=Max(Year))'}>})>*<Year={'<=$(=Max(Year))>=$(=Max(Year)-1)'},Customer=p({<Year={'$(=Max(Year)-1)'}>})>}sales)

PFA

Re: Loyal customer per years using indirect set analysis

Anonymous — Thu, 27 Aug 2015 12:02:55 GMT

Thank a Lot !!! is exactly what i want

Thank again

Re: Loyal customer per years using indirect set analysis

Anonymous — Thu, 27 Aug 2015 12:14:35 GMT

Thenk for your help Jonathan,

what i wanted is tresesco solution.