topic Re: Convert To Set Analysis in QlikView

Convert To Set Analysis

ksomosera10 — Tue, 29 Aug 2017 06:14:02 GMT

Hi,

Can you help me out? I have this working expression:

=NUM(SUM(IF([CategoryGroup] = 'Project' AND "MONTH([CutoffMonth]&'/1/1932')" = Month(Today()), duration)), '#,##0')

It shows the Sum of "Duration" on works rendered on all the projects for the current month...

Can you help me out transforming it into a set analysis?

I looked around the internet but I couldn't find any ways to convert it into set analysis.

It is required that I use set analysis instead of this that's why I want to convert these.

Thanks for the help.

Re: Convert To Set Analysis

petter — Tue, 29 Aug 2017 07:00:36 GMT

Using a Set Expression it should look something like this:

Num( Sum( { < [CategoryGroup] = {'Project'} , [UniqueRowId] = { "=Month([CutOffMonth]&'/1/1/1932')=Month(Today() " } > } duration ) , '#,##0' )

The search expression with a comparison needs to have a field that uniquely identifies each row hence I called this field [UniqueRowId] which is needed to be sure that the right rows are filtered/selected. If you dont have such a field you can create one in your load script by using RowNo() AS UniqueRowId.

Re: Convert To Set Analysis

ksomosera10 — Tue, 29 Aug 2017 07:06:14 GMT

Can I use the primary key as uniqueRowID?

Re: Convert To Set Analysis

Kushal_Chawda — Tue, 29 Aug 2017 07:16:51 GMT

It's better to create the Flag in script for If condition, which will make set analysis more simple and optmized

LOAD *,

IF([CategoryGroup] = 'Project' AND "MONTH([CutoffMonth]&'/1/1932')" = Month(Today()), 1) as Flag

FROM table;

Now you can use below expression

NUM(SUM({<Flag={1}>}duration), '#,##0')

Re: Convert To Set Analysis

petter — Tue, 29 Aug 2017 07:25:25 GMT

Yes you can and this expression is probably better than the first i suggested:

Num( Sum( { < [CategoryGroup] = {'Project'} , [UniqueRowId] = { "=CutOffMonth=Num(Month(Today()))" } > } duration ) , '#,##0' )

Not necessary to use the Month() function as much as the CutOffMonth is numeric in the first place

Re: Convert To Set Analysis

petter — Tue, 29 Aug 2017 07:30:00 GMT

You're right about "flags" being more optimal. I would further suggest that the generic name "flag" should be avoided and use a more descriptive name for the field like:

isProjectAndCutOffMonth

so it turns into:

NUM(SUM({<isProjectAndCutOffMonth={1}>}duration), '#,##0')

Re: Convert To Set Analysis

Kushal_Chawda — Tue, 29 Aug 2017 07:31:20 GMT

petter-s

Just curious to know that how this expression is better then Flag method. Just wanted to know your input as know sometimes Flag is better and sometimes set analysis.

Re: Convert To Set Analysis

petter — Tue, 29 Aug 2017 07:36:50 GMT

A flag/indicator is better speedwise. Sometimes the logic needs to be changed by interactions in the UI by the user. Then it is hard to create a load script logic to create a flag in the first place.

The flag is performance wise faster as it is precalculated. The Set Expression is more flexible. The Set Expression can always make use of the associative model whereas a flag generated in the load script does not have that available.

Re: Convert To Set Analysis

Kushal_Chawda — Tue, 29 Aug 2017 07:49:57 GMT

Thanks for the explanation