Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
aetingu12
Contributor

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

Re: Selection from a selection, is that possible?

That might be possible like this

Dimension

Month

Expression

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

16 Replies

Re: Selection from a selection, is that possible?

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
Contributor

Re: Selection from a selection, is that possible?

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

Re: Selection from a selection, is that possible?

That might be possible like this

Dimension

Month

Expression

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

aetingu12
Contributor

Re: Selection from a selection, is that possible?

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

Re: Selection from a selection, is that possible?

aetingu12
Contributor

Re: Selection from a selection, is that possible?

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

Thanks a lot again.

aetingu12
Contributor

Re: Selection from a selection, is that possible?

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

Re: Selection from a selection, is that possible?

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
Contributor

Re: Selection from a selection, is that possible?

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?

Community Browser