Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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])
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
Another one
Sum({<[Account Number] = E({<[ABC Tax Year] = E({<[ABC Tax Year] = {'$(=Max([ABC Tax Year]))'}>})>}), IsInTYTD={'Yes'},>}[Total Outstanding])
Hi sunny
Can you please explain this. I am not sure i understand how you applied the logic here.
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
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.
Yes it is
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
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....
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