Selection from a selection, is that possible?

[aetingu12]

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

[sunny_talwar]

How is Month created in the script? Do you create it in a master calendar script? Not so good looking alternative would be

Sum({<Client = {"=Max({<Month = {""=Max(Date#(Month, 'MMM')) = Max(TOTAL Date#(Month, 'MMM'))""}>}Flag) = 1"}>}Balance)

[aetingu12]

I am not sure.  We have a Summary_Date field coming from a table in a database.  So we have Summary_Date as 31-05-2017 coming in as part of the main table.  I would like to look up the latest Summary_Date (31-05-2107 in this case).

Is month a system variable? or a field?  I am trying to find a way to attach the file.

[sunny_talwar]

Try this

Sum({<Client = {"=Max({<Summary_Date = {""=Max(Date#(Summary_Date , 'DD/MM/YYYY')) = Max(TOTAL Date#(Summary_Date , 'DD/MM/YYYY'))""}>}Flag) = 1"}>}Balance)

[aetingu12]

Thank you! Works like a charm.  If the user were to add their own lists and filters etc.  Would it still filter on the latest flag for the latest summary_date? i.e. if data had another column for color of the client and they wanted to filter for red, would all these still work?

Set Analysis is very confusing!  Your help is extremely appreciated!

Regards,

Aksel

[sunny_talwar]

I won't really know what selections you mean unless I see. Difficult to say about things without getting a chance to look at them

[aetingu12]

I was becoming a bit lazy by asking everything from you.  I just tried and it worked!  Thank you very very much!

[sunny_talwar]

Nice....