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

[mdmukramali]

Hi Lee,

Can you try something like below:

Sum({< [Account Number]= E({<[ABC Tax Year]={2013,2014,2015,2016}>}), [ABC Tax Year]={2017},IsInTYTD={'Yes'}>}[Total Outstanding])

OR

sum({<[Account Number]=p({<[ABC Tax Year]={2017}>}[Account Number]), [Account Number]=e({<[ABC Tax Year]={2013,2014,2015,2016}>}[Account Number])>}[Total Outstanding])

if it's not helpful then please can you attach the sample data?

Thanks,

Mukram

[sunny_talwar]

Another one

Sum({<[Account Number] = E({<[ABC Tax Year] = E({<[ABC Tax Year] = {'$(=Max([ABC Tax Year]))'}>})>}), IsInTYTD={'Yes'},>}[Total Outstanding])

[pradosh_thakur]

Hi sunny

Can you please explain this. I am not sure i understand how you applied the logic here.

[sunny_talwar]

I am trying to make the exclusion of Account Numbers which are present in years 2013-2016 as dynamic as they could.... I guess it can be simplified to this

Sum({<[Account Number] = E({<[ABC Tax Year] = {'$(='<=' & Max([ABC Tax Year]))'}>}), IsInTYTD={'Yes'},>}[Total Outstanding])

But my goal below (in red) was to exclude max year from ABC Tax Year

Sum({<[Account Number] = E({<[ABC Tax Year] = E({<[ABC Tax Year] = {'$(=Max([ABC Tax Year]))'}>})>}), IsInTYTD={'Yes'},>}[Total Outstanding])

and then exclude Account Number present in those years from the calculations

Sum({<[Account Number] = E({<[ABC Tax Year] = E({<[ABC Tax Year] = {'$(=Max([ABC Tax Year]))'}>})>}), IsInTYTD={'Yes'},>}[Total Outstanding])

Trying to do similar to what mdmukramali, but just tried to make the max year more dynamic

[pradosh_thakur]

that's great application of e() function . Never knew it can be used as this.

The comma after {'Yes'} is it there by mistake ?

Thank you stalwar1 Learning new things from you everyday.

[sunny_talwar]

Yes it is

[lnoble3023]

Hi,

Apologies for the slow response.  I was away for a couple of days at the new year, then had been working and looking at what you guys had provided as answers…with a coworker.  Thanks so much for the responses – it is sincerely appreciated.

I was able to get Mukram’s script to work.  Then I found 3 more filters I needed to add.  Also adding ‘not’ [Brokerage], [Invoice Paid] = {‘no’}, and [Total Outstanding] > 0.  When I add those, I can’t seem to get it to work.

I again really appreciate both of your help and time on this.  If you are still available to help me, I’ve added a spreadsheet with some test data – thinking this might make it easier.  I’m pretty stumped on what to do next  :o( Thank you.

Lee

[sunny_talwar]

This seems to be working and give the same total as the expected output...

Sum({<[Account Number] = E({<[ABC Tax Year] = E({<[ABC Tax Year] = {'$(=Max([ABC Tax Year]))'}>})>}), Brokerage = {'no'}, [Invoice Paid] = {'no'}>}[Total Outstanding])

Only the highlighed rows (such as Account Number 88274) are grouped into a single row because in a straight table the dimensions are usually grouped into one... you want to see them as separate rows? then add a dimension which can make them unique....

[lnoble3023]

Hi Sunny,

I tried this, and it works perfectly!  Thank you sooo much for the help.  I would have never figured this out without your help.  Thanks again.

Do you by chance have any recommendations of sites or trainings that I could take to help me with better learning Qlikview scripting??  Any ideas would be welcomed. Again, many thanks!!

Lee