Trouble on Set Analysis

[lnoble3023]

Hi,

I'm a relative newbie to Qlikview, and I'd really appreciate some help on a Set Analysis expression I'm trying to write.  The basics of what I'm trying to write is this:

Trying to get a Sum of Total Outstanding for 'ABC Tax Year', for only brand new customers (Account Number).  In my data, I have tax years of 2013 - 2017 (current year).  I want to grab only Total Outstanding for those new customers (Account Number) who have not purchased in 2013 - 2016.  Example, 'if' someone bought in 2016 and again this year, per se, I would NOT want to include their Outstanding balance.  And the 'IsInTYTD' field is a 'Yes', as it is a field that identifies the item is purchased via transaction date of 'this' tax year.

My expression at present is below.  I am not sure if I can use field [ABC Tax Year] twice in the definition??  And when I'm deciding to put set analysis 'inside' of another set analysis, I'm confused.  Expression is below:

=num(Sum({<[ABC Tax Year] = {'$(=max([ABC Tax Year]))'},IsInTYTD={'Yes'},Count({<[ABC Tax Year]={'2013','2014','2015','2016'}>} DISTINCT [Account Number]) <= 0 >} [Total Outstanding]), '$#,##0')

thanks again for any help you might could provide - Happy New Year, ALL 

Lee

[sunny_talwar]

I am glad it worked... I would request you to close this thread down by marking correct and any helpful responses

Qlik Community Tip: Marking Replies as Correct or Helpful

With regards to the training part, I think the best way for you to learn would be to take active part in the community. You will be surprised how quickly you will learn new things when you try to help others.

In addition, read the blogs by hic‌ and specifically for Set Analysis, you can read this

Set Analysis: syntaxes, examples