topic Re: Trouble on Set Analysis in QlikView

Trouble on Set Analysis

lnoble3023 — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Trouble on Set Analysis

mdmukramali — Sun, 31 Dec 2017 19:43:30 GMT

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

Re: Trouble on Set Analysis

sunny_talwar — Sun, 31 Dec 2017 22:33:31 GMT

Another one

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

Re: Trouble on Set Analysis

pradosh_thakur — Mon, 01 Jan 2018 04:28:48 GMT

Hi sunny

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

Re: Trouble on Set Analysis

sunny_talwar — Mon, 01 Jan 2018 17:09:02 GMT

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

Re: Trouble on Set Analysis

pradosh_thakur — Mon, 01 Jan 2018 17:25:12 GMT

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.

Re: Trouble on Set Analysis

sunny_talwar — Mon, 01 Jan 2018 18:54:50 GMT

Yes it is

Re: Trouble on Set Analysis

lnoble3023 — Mon, 08 Jan 2018 07:06:36 GMT

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

Re: Trouble on Set Analysis

sunny_talwar — Mon, 08 Jan 2018 09:57:09 GMT

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....

Re: Trouble on Set Analysis

lnoble3023 — Wed, 10 Jan 2018 02:31:18 GMT

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

Re: Trouble on Set Analysis

sunny_talwar — Wed, 10 Jan 2018 09:05:15 GMT

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