topic Re: Complex set analysis with joins in QlikView

Complex set analysis with joins

phuelish — Mon, 14 May 2018 16:15:53 GMT

Hi folks...hoping you can help with this one, as I'm stumped.

I'm monitoring the results of a sequential program rollout where each state has a different implementation date. I only have access to the front end of the QlikView tool I'm using, so can't make any changes to the load script. I have a problem with the set analysis where one of the equations is cutting off after the first implementation date.

The one I'm having problems with is designed to measure all of the jurisdictions that do not yet have the program implemented. Basically, I'm taking everything except Texas and subtracting all of the states based on their implementation date. The results aren't showing correctly, however, as they are null for anything after the 7/16/2015 implementation even though it was only in one state.

Any help you can provide to correct my syntax or show me a better way is greatly appreciated.

- Frank

  
Sum({<[Delay_Reason]={'Out of stock'},NR_Months={'-1','0','1','2','3'},[State Name]-={'TEXAS'}>
  
-(<[State Name]={'INDIANA'},[Month Name]={">07/16/2015"}>
  
+<[State Name]={'ALABAMA','ARIZONA','ILLINOIS','MISSOURI','NEW YORK'},[Month Name]={">07/28/2016"}>
  
+<[State Name]={'COLORADO','FLORIDA','LOUISIANA','OHIO','OREGON','PENNSYLVANIA'},[Month Name]={">08/25/2016"}>
  
+<[State Name]={'MAINE','NEVADA','OKLAHOMA','WASHINGTON'},[Month Name]={">09/23/2016"}>
  
+<[State Name]={'MARYLAND','TENNESSEE','VIRGINIA'},[Month Name]={">11/11/2016"}>
  
+<[State Name]={'IDAHO','IOWA','KANSAS','MISSISSIPPI','MEW MEXICO','UTAH'},[Month Name]={">12/15/2016"}>
  
+<[State Name]={'ALASKA','MINNESOTA','NEBRASKA','VERMONT'},[Month Name]={">05/25/2017"}>
  
+<[State Name]={'CALIFORNIA','KENTUCKY','SOUTH DAKOTA','WISCONSIN','ARKANSAS'},[Month Name]={">09/22/2017"}>
  
+<[State Name]={'CONNECTICUT','DISTRICT OF COLUMBIA','MICHIGAN','WEST VIRGINIA'},[Month Name]={">03/29/2018"}>)}
  
DISTINCT_SALE)
  
/SUM({<[State Name]=-{'TEXAS'}>
  
-(<[State Name]={'INDIANA'},[Month Name]={">07/16/2015"}>
  
+<[State Name]={'ALABAMA','ARIZONA','ILLINOIS','MISSOURI','NEW YORK'},[Month Name]={">07/28/2016"}>
  
+<[State Name]={'COLORADO','FLORIDA','LOUISIANA','OHIO','OREGON','PENNSYLVANIA'},[Month Name]={">08/25/2016"}>
  
+<[State Name]={'MAINE','NEVADA','OKLAHOMA','WASHINGTON'},[Month Name]={">09/23/2016"}>
  
+<[State Name]={'MARYLAND','TENNESSEE','VIRGINIA'},[Month Name]={">11/11/2016"}>
  
+<[State Name]={'IDAHO','IOWA','KANSAS','MISSISSIPPI','MEW MEXICO','UTAH'},[Month Name]={">12/15/2016"}>
  
+<[State Name]={'ALASKA','MINNESOTA','NEBRASKA','VERMONT'},[Month Name]={">05/25/2017"}>
  
+<[State Name]={'CALIFORNIA','KENTUCKY','SOUTH DAKOTA','WISCONSIN','ARKANSAS'},[Month Name]={">09/22/2017"}>
  
+<[State Name]={'CONNECTICUT','DISTRICT OF COLUMBIA','MICHIGAN','WEST VIRGINIA'},[Month Name]={">03/29/2018"}>)} 
  
total<[Month Name],[State Name]> 
  
DISTINCT_SALE)

Re: Complex set analysis with joins

sunny_talwar — Mon, 14 May 2018 16:19:46 GMT

Would you be able to share a sample to take a look at this?

Re: Complex set analysis with joins

phuelish — Mon, 14 May 2018 16:26:07 GMT

Unfortunately not. I'm only able to access the front end as a user. I can't do anything other than create new objects and sheets.

Re: Complex set analysis with joins

joseduque — Mon, 14 May 2018 16:35:11 GMT

Hi Frank,

First off Set Analysis works with all the parameters in the same {< >} So if you need to use different parameters you need to do one Set Analysis at a time.

Second Just to make clear is [Month Name] a Date field or just a Month Field? Example jan, feb, mar

cause you're applying a date to [Month Name]={">07/16/2015"}

Third I reccomend you to split the expression to see if is working properly in the conditions, so you can find the one that is not working properly.

Example:

Sum({<[Delay_Reason]={'Out of stock'},NR_Months={'-1','0','1','2','3'},[State Name]-={'TEXAS'}>} DISTINCT_SALE)

Sum({<[State Name]={'INDIANA'},[Month Name]={">07/16/2015"}>} DISTINCT_SALE)

Sum({<[State Name]={'ALABAMA','ARIZONA','ILLINOIS','MISSOURI','NEW YORK'},[Month Name]={">07/28/2016"}>} DISTINCT_SALE)

Hope This Helps.

Jose

Re: Complex set analysis with joins

phuelish — Mon, 14 May 2018 18:19:44 GMT

Hi Jose,

Thanks for taking a look at this.

For your second question, [Month Name} is a dual field with the text date (formatted mmm-yy) and numeric date.

For your first and third question, the subtraction and addition signs are join operators and not mathematical operators:

<set 1> + <set 2> = all members of set 1 + all members of set 2

<set 1> - <set 2> = all members of set 1 unless they are members of set 2

<set 1> * <set 2> = all members that are in both sets

<set 1> / <set 2> = all members except those that are in both sets

It is possible to do each set separately, but would require including the criteria for the first set within the criteria of each subsequent set:

Sum({<[Delay_Reason]={'Out of stock'},NR_Months={'-1','0','1','2','3'},[State Name]-={'TEXAS'}>} DISTINCT_SALE)

Sum({<[State Name]={'INDIANA'},[Month Name]={">07/16/2015"},[Delay_Reason]={'Out of stock'},NR_Months={'-1','0','1','2','3'}>} DISTINCT_SALE)

Sum({<[State Name]={'ALABAMA','ARIZONA','ILLINOIS','MISSOURI','NEW YORK'},[Month Name]={">07/28/2016"},[Delay_Reason]={'Out of stock'},NR_Months={'-1','0','1','2','3'}>} DISTINCT_SALE)

I was trying to avoid this approach and hopefully learn how to properly structure my set joins in the process.

Thanks!

-Frank

Re: Complex set analysis with joins

marcus_sommer — Tue, 15 May 2018 08:06:18 GMT

I doubt that there would be any better approach within the UI. Of course you could replace some parts or all of the set analysis conditions with outside-conditions like if-loops or match() and maybe even using this within an aggr() - but I don't believe it would be easier in any way or be more performant. Rather the opposite would be happens.

But the suggestion from Frank to split the expression into several parts might an alternatively especially to find any errors more easily and to ensure that the data are really like you expect them (by adding them together again I suggest to use rangesum() instead of using the operators "+" or "-".

Beside this is it in my opinion not really sensible to solve such a task within the UI else to create an appropriate flag-field within the script which could be easily reached with a mapping similar to this eample:

map:

mapping load * inline [

State Name, Month Name

INDIANA, 07/16/2015

...

];

facts:

load *, -([Month Name] > applymap('map', [State Name], 1000000)) as Flag

from Source;

This meant I think what you need is a better collaboration with the guys which are responsible for the datamodels ...

- Marcus

Re: Complex set analysis with joins

phuelish — Tue, 15 May 2018 14:35:32 GMT

Thanks everyone for the ideas and inputs. I'll try breaking it down into smaller pieces as Jose suggested...maybe I can figure out what I'm doing wrong. If I come up with a solution, I'll update the thread.

Re: Complex set analysis with joins

phuelish — Tue, 15 May 2018 18:42:01 GMT

I was able to get it working with a bit of manipulation, employing Jose's suggestion for a portion of it. For some reason, the set exclusion operator appears to interpret the exclusion set elements separately rather than together. Multiple elements within a set modifier are usually interpreted as "AND" conditions; when using the exclusion operator, the exclusion set is getting interpreted as "OR" conditions. I don't know if this is my syntax or what?

the work-around:

(Sum({<[Delay_Reason]={'Out of stock'},NR_Months={'-1','0','1','2','3'},[State Name]-={'TEXAS'}> DISTINCT_SALE)
-(SUM(<[Delay_Reason]={'Out of stock'},NR_Months={'-1','0','1','2','3'}>*(<[State Name]={'INDIANA'},[Month Name]={">07/16/2015"}>
+<[State Name]={'ALABAMA','ARIZONA','ILLINOIS','MISSOURI','NEW YORK'},[Month Name]={">07/28/2016"}>
+<[State Name]={'COLORADO','FLORIDA','LOUISIANA','OHIO','OREGON','PENNSYLVANIA'},[Month Name]={">08/25/2016"}>
+<[State Name]={'MAINE','NEVADA','OKLAHOMA','WASHINGTON'},[Month Name]={">09/23/2016"}>
+<[State Name]={'MARYLAND','TENNESSEE','VIRGINIA'},[Month Name]={">11/11/2016"}>
+<[State Name]={'IDAHO','IOWA','KANSAS','MISSISSIPPI','MEW MEXICO','UTAH'},[Month Name]={">12/15/2016"}>
+<[State Name]={'ALASKA','MINNESOTA','NEBRASKA','VERMONT'},[Month Name]={">05/25/2017"}>
+<[State Name]={'CALIFORNIA','KENTUCKY','SOUTH DAKOTA','WISCONSIN','ARKANSAS'},[Month Name]={">09/22/2017"}>
+<[State Name]={'CONNECTICUT','DISTRICT OF COLUMBIA','MICHIGAN','WEST VIRGINIA'},[Month Name]={">03/29/2018"}>)}
DISTINCT_SALE))
/(SUM({<[State Name]=-{'TEXAS'}> DISTINCT_SALE)
-(SUM(<[State Name]={'INDIANA'},[Month Name]={">07/16/2015"}>
+<[State Name]={'ALABAMA','ARIZONA','ILLINOIS','MISSOURI','NEW YORK'},[Month Name]={">07/28/2016"}>
+<[State Name]={'COLORADO','FLORIDA','LOUISIANA','OHIO','OREGON','PENNSYLVANIA'},[Month Name]={">08/25/2016"}>
+<[State Name]={'MAINE','NEVADA','OKLAHOMA','WASHINGTON'},[Month Name]={">09/23/2016"}>
+<[State Name]={'MARYLAND','TENNESSEE','VIRGINIA'},[Month Name]={">11/11/2016"}>
+<[State Name]={'IDAHO','IOWA','KANSAS','MISSISSIPPI','MEW MEXICO','UTAH'},[Month Name]={">12/15/2016"}>
+<[State Name]={'ALASKA','MINNESOTA','NEBRASKA','VERMONT'},[Month Name]={">05/25/2017"}>
+<[State Name]={'CALIFORNIA','KENTUCKY','SOUTH DAKOTA','WISCONSIN','ARKANSAS'},[Month Name]={">09/22/2017"}>
+<[State Name]={'CONNECTICUT','DISTRICT OF COLUMBIA','MICHIGAN','WEST VIRGINIA'},[Month Name]={">03/29/2018"}>)}
total<[Month Name],[State Name]>
DISTINCT_SALE))