Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Highlighted
ksomosera10
Contributor II

Convert To Set Analysis

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.

1 Solution

Accepted Solutions

Re: Convert To Set Analysis

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')

8 Replies
MVP
MVP

Re: Convert To Set Analysis

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.

ksomosera10
Contributor II

Re: Convert To Set Analysis

Can I use the primary key as uniqueRowID?

Re: Convert To Set Analysis

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')

MVP
MVP

Re: Convert To Set Analysis

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

MVP
MVP

Re: Convert To Set Analysis

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

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.

MVP
MVP

Re: Convert To Set Analysis

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

Thanks for the explanation