Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
aetingu12
Creator
Creator

Selection from a selection, is that possible?

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

1 Solution

Accepted Solutions
sunny_talwar

That might be possible like this

Dimension

Month

Expression

Sum({<Client = {"=Max(Flag) = 1"}>}Balance)

View solution in original post

16 Replies
sunny_talwar

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

aetingu12
Creator
Creator
Author

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

sunny_talwar

That might be possible like this

Dimension

Month

Expression

Sum({<Client = {"=Max(Flag) = 1"}>}Balance)

aetingu12
Creator
Creator
Author

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

sunny_talwar

aetingu12
Creator
Creator
Author

Thank you very much.  I assume I can tag more criteria to this right. i.e. Flag1 , Flag2 etc.

Thanks a lot again.

aetingu12
Creator
Creator
Author

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

sunny_talwar

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

aetingu12
Creator
Creator
Author

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?