Announcements
Marching toward a simplified navigation! READ ON
cancel
Showing results for
Did you mean:
Creator 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

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

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
Partner - Champion III

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.

Creator II
Author

Can I use the primary key as uniqueRowID?

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

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

Partner - Champion III

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

Partner - Champion III

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

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.

Partner - Champion III

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.

Thanks for the explanation

Community Browser