topic Re: Selection from a selection, is that possible? in QlikView

Selection from a selection, is that possible?

aetingu12 — Fri, 07 Jul 2017 19:29:33 GMT

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

Re: Selection from a selection, is that possible?

sunny_talwar — Fri, 07 Jul 2017 19:35:13 GMT

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

Re: Selection from a selection, is that possible?

aetingu12 — Fri, 07 Jul 2017 19:40:27 GMT

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?

sunny_talwar — Fri, 07 Jul 2017 19:41:59 GMT

That might be possible like this

Dimension

Month

Expression

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

Re: Selection from a selection, is that possible?

aetingu12 — Fri, 07 Jul 2017 19:57:12 GMT

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?

sunny_talwar — Fri, 07 Jul 2017 19:57:58 GMT

This is a good one

Set Analysis: syntaxes, examples

Re: Selection from a selection, is that possible?

aetingu12 — Fri, 07 Jul 2017 20:01:00 GMT

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

Thanks a lot again.

Re: Selection from a selection, is that possible?

aetingu12 — Mon, 10 Jul 2017 18:25:53 GMT

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?

sunny_talwar — Mon, 10 Jul 2017 18:29:08 GMT

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

Re: Selection from a selection, is that possible?

aetingu12 — Mon, 10 Jul 2017 18:42:50 GMT

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?

Re: Selection from a selection, is that possible?

sunny_talwar — Mon, 10 Jul 2017 18:46:45 GMT

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)

Re: Selection from a selection, is that possible?

aetingu12 — Mon, 10 Jul 2017 19:00:13 GMT

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.

Re: Selection from a selection, is that possible?

sunny_talwar — Mon, 10 Jul 2017 19:02:08 GMT

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)

Re: Selection from a selection, is that possible?

aetingu12 — Mon, 10 Jul 2017 19:07:10 GMT

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

Re: Selection from a selection, is that possible?

sunny_talwar — Mon, 10 Jul 2017 19:13:11 GMT

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

Re: Selection from a selection, is that possible?

aetingu12 — Mon, 10 Jul 2017 19:17:11 GMT

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

Re: Selection from a selection, is that possible?

sunny_talwar — Mon, 10 Jul 2017 19:18:09 GMT

Nice....