Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Dear all,
I am an SQL user, so the solution to my problem on SQL would be simple, but I would like to know if this could be done within Qlikview.
You have a table:
Month Client Balance Flag
May A 100 1
May B 200 1
April A 50 1
April B 150 0
I wan't to get the clients in the latest available month (A and B) and get their historic balances. In SQL that would be a select within another select. (to give me history for both A and B, excluding C)
The issue in Qlikview is that I apply a filter on the flag for 1 so I need data where Flag is one in May. As with every corporation, there are data flaws, so if this is run the data I would get would be 300 for May and only 50 for April. So I want to use the filter to get the right set of clients to my latest month(or designated) and once I have this, pull the data for all within this distinct clients list to give me 300 for May and 200 for April including Client B who is flagged zero.
Complex ask, but help is very much appreciated.
Regards,
Aksel
That might be possible like this
Dimension
Month
Expression
Sum({<Client = {"=Max(Flag) = 1"}>}Balance)
Not sure where you are looking to do this (script or front end), but a front end solution can look like this
Dimension
Month
Expression
Sum({<Flag = {1}>}Balance) -> Will give you 300 for May and 50 for April
Sum(Balance) -> Will give you 300 for May and 200 for April
Thank you very much for the quick turn around.
Would this work if my data table was :
Month Client Balance Flag
May A 100 1
May B 200 1
April A 50 1
April B 150 0
April C 200 0
And still give me the same answer... May 300 April 200 (and exclude C)
Really appreaciate your time.
Thanks,
Aksel
That might be possible like this
Dimension
Month
Expression
Sum({<Client = {"=Max(Flag) = 1"}>}Balance)
You sir.... are a legend! I guess I need to read up on Set Analysis to understand the workings. Any doc to recommend?
What does {"=Max(Flag) = 1"}> syntax do? I mean how does it read? get me the sum of all clients where the latest flag equals 1?
Thank you very much for your help!
Regards,
Aksel
This is a good one
Thank you very much. I assume I can tag more criteria to this right. i.e. Flag1 , Flag2 etc.
Thanks a lot again.
Can I ask a follow up question, if I may.
After reading the document you have sent, I understand that what the code does. It looks at the clients where the latest flag is equal to 1. Given my earlier example, it works perfectly. But how would I solve this issue.
Same table with an additional data point.
Month Client Balance Flag
May A 100 1
May B 200 1
May C 150 0
April A 50 1
April B 150 0
April C 200 1
If I run your expression it will find the clients and the months where the flag was equal to one. So for MAY I will get Client A and Client B which is ok 300, but for April I will get Client A , Client B and Client C totalling to 350.
What I would really want would be to look at the Flag where it is 1 in the latest month so in May and get the data for it historically.
So who is flagged 1 in May = Client A and Client B, so I would expect to have May to be 300 (A and B) and April to be 200 (A and B even thought B is 0 in April, because it is a one in May).
Basically, I want to pull data for only those client in the max month where the flag is 1.
Is this doable?
Really appreciate your help.
Regards,
Aksel
May be this
Sum({<Client = {"=Max({<Month = {'$(=Max(Month))'}>}Flag) = 1"}>}Balance)
Where Month is created in the script like this
Month(Date) as Month
I am using a version of Qlikview that is locked down at work. So I do not have access to scripting. Is there a workaround? or a way to handle it within expressions?