topic Re: Set analysis help requested in QlikView

Set analysis help requested

dukane24 — Thu, 12 Dec 2013 19:29:05 GMT

I am using the expression below in a bar chart. "Pastdue" and "Update Interval" are both numerical values calculated in the load script. I am trying to count instances where the Pastdue value is greater than the Update Interval value. However, I have verified that it is only counting the "Defect ID" and ignoring the "greater than" condition altogether. I am fairly new to set analysis so it is quite possible there is something I am overlooking with my syntax. Any help would be appreciated.

count({$<[Pastdue]={">"$(Update Interval)}>}[Defect ID])

Thank you,

-Nate

Re: Set analysis help requested

Nicole-Smith — Thu, 12 Dec 2013 19:31:54 GMT

count({$<[Pastdue]={'>$(UpdateInterval)'}>}[Defect ID])

**EDIT: This will only work if UpdateInterval is a variable.

Re: Set analysis help requested

Nicole-Smith — Thu, 12 Dec 2013 19:35:39 GMT

Actually, it seems like what you're asking for isn't going to work with what I posted either. Can you post a sample .qvw?

Preparing examples for Upload - Reduction and Data Scrambling

Re: Set analysis help requested

Thu, 12 Dec 2013 19:37:09 GMT

I think you can use a simple IF statement instead of Set Analysis for what you are trying to do.

SUM( IF (PastDue > [Update Interval], 1))

Quite simply for every row where PastDue is great than [Update Interval] the system will use the value 1 and them sum them up. If you have a scenario where you only want to count the distinct Defect ID's then you can modify the command slightly:

COUNT(DISTINCT IF(PastDue > [Update Interval], [Defect ID])

In that case it outputs the value Defect ID and then it determines how many of the values you output were unique.

Set Analsysis is only needed where have more complicated scenarios like you want to ignore certain filter selections a user has made or force it to use other filters.

Re: Set analysis help requested

Thu, 12 Dec 2013 19:48:44 GMT

Hello,

Maybe you can try this:

count({$<[Pastdue]={">$(Update Interval)"}>} DISTINCT [Defect ID])

IF([Pastdue]>$(Update Interval),

count(DISTINCT [Defect ID])

)

but is better

COUNT(DISTINCT IF([PastDue] > [Update Interval], [Defect ID])

as Dalton Said.

regards.

Re: Set analysis help requested

dukane24 — Thu, 12 Dec 2013 20:24:33 GMT

Thanks Dalton! This worked. I am new to Qlikview and to set analysis. I was just thinking for charts in Qlikview, set analysis is what needed to be used as that is all I have found when searching for answers to my Qlikeivew questions.

I do have a couple conditions I need to throw in to exclude results for some fields but I was able to tack on some ANDs in my IF statment and it worked.

This problem has been pestering me for a while now so your help is much appreciated.

Thanks,

-Nate

Re: Set analysis help requested

Thu, 12 Dec 2013 21:17:55 GMT

Glad it was helpful.

Set Analysis can be very complicated or it can be easy once you get past the strange syntax. Let's say you want to see sales figures just for 2013 regardless of what year the end user may have selected you would do the following:

=SUM({$<START_Y={2013}>}Sales)

The $ on the far left indicates that existing filters should be used, except where you override them. Instead of the $ you can alternatively use a 1 which says "In this case don't trust anything that the users may have clicked and ignore all filters like Region, Sales person etc and produces the total of all sales for 2013

=SUM({1<START_Y={2013}>}Sales)

You can also use multiple fields if you want all Sales for just the SOUTH region.

=SUM({1<START_Y={2013}, Region = {'SOUTH'}>}Sales)

You can also specify to ignore a particular filter without setting the value. So lets say you want to honor settings for product and sales person, but you only want 2013 data and you have to see it for all regions. You can use the expression below:

=SUM({$START_Y={2013}, Region = >}Sales)

Start slow using things like this and then you can really enhance SET ANALYSIS by utilizing more complicated expressions. Assume you used the above and it worked great for all 365 days this year, but what would happen on January 1, 2014? It would continue to show the sales for 2013 even though your label might have said "Current YTD Sales". You can utilize an expression to get the YEAR for whatever is returned by the TODAY function. So it would work for 2013 and would immediately rollover to 2014 on January 1.

=SUM({$<START_Y={$(=Year(Today()))}>}Sales)

Syntax starts getting harder to read but when that happens just back off and isolate the elements and try 1 change at a time and come back to your simple expressions and work up.