Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Sales this Year but not Previous two years

Hi,

I have succesfully used this expression to calculate those customers who purchased a certain product this year but not last year. How would I modify this to calculate for sales this year but NOT last year OR the year before? (CUSTABRNO=CUSTOMER)

sum

( {<CUSTABRNO = p({<Year={$(=max(Year))}>})-p({<Year={$(=max(Year)-1)}>} CUSTABRNO)>}AMOUNT) ;





1 Solution

Accepted Solutions
johnw
Champion III
Champion III

I'd guess you could just add one more p() expression to it:

sum({<CUSTABRNO=p({<Year={$(=max(Year))}>})
-p({<Year={$(=max(Year)-1)}>} CUSTABRNO)
-p({<Year=($(=max(Year)-2)}>} CUSTABRNO)>} AMOUNT)

Or list both years in one p() expression:

sum({<CUSTABRNO=p({<Year={$(=max(Year))}>})
-p({<Year={$(=max(Year)-1),$(=max(Year)-2)}>} CUSTABRNO)>} AMOUNT)

Or check for a range of years (only really useful if you have more than two):

sum({<CUSTABRNO=p({<Year={$(=max(Year))}>})
-p({<Year={"<=$(=max(Year)-1) >=$(=max(Year)-2)"}>} CUSTABRNO)>} AMOUNT)

View solution in original post

5 Replies
johnw
Champion III
Champion III

I'd guess you could just add one more p() expression to it:

sum({<CUSTABRNO=p({<Year={$(=max(Year))}>})
-p({<Year={$(=max(Year)-1)}>} CUSTABRNO)
-p({<Year=($(=max(Year)-2)}>} CUSTABRNO)>} AMOUNT)

Or list both years in one p() expression:

sum({<CUSTABRNO=p({<Year={$(=max(Year))}>})
-p({<Year={$(=max(Year)-1),$(=max(Year)-2)}>} CUSTABRNO)>} AMOUNT)

Or check for a range of years (only really useful if you have more than two):

sum({<CUSTABRNO=p({<Year={$(=max(Year))}>})
-p({<Year={"<=$(=max(Year)-1) >=$(=max(Year)-2)"}>} CUSTABRNO)>} AMOUNT)

Not applicable
Author

Thanks John, worked out perfectly.

Another quick question, is there a quick way of setting the year to be Fiscal Year? using some sort of SET function?

Currently when I click 2010 it takes the Calendar Year. I want my selection of 2010 to start October 1st. 2010.

tmumaw
Specialist II
Specialist II

Hi Les,

I am trying to do the same thing I think you are. Have you been able to count the number of customers who purchased an item last year but not this year? I am trying to create a customer retention report and I am looking for the syntax to count customers. (something like your sum).

Thanks
Thom

Not applicable
Author

What I was doing was, my user would select a supplier/product and then the list would generate showing customers who purchased last year but not this year as well as the amount from last year that was not purchased this year.

I also used this same formula but with selecting a customer and showing which products they purchased last year but not this year.

I have not attempted to do anything with counting the number of customers however i'm sure something could be figured out using what I did and the COUNT() function.

johnw
Champion III
Champion III


Les Backman wrote:Another quick question, is there a quick way of setting the year to be Fiscal Year? using some sort of SET function?
Currently when I click 2010 it takes the Calendar Year. I want my selection of 2010 to start October 1st. 2010.


I'd just create a Fiscal Year field. For instance:

year(YourDate)+(month(YourDate)<10) as "Fiscal Year"

Or:

date(yearstart(YourDate,0,10),'YYYY') as "Fiscal Year"