Convert To Set Analysis

[ksomosera10]

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.

[petter]

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]

Can I use the primary key as uniqueRowID?

[Kushal_Chawda · Accepted solution]

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

[petter]

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

[petter]

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

[Kushal_Chawda]

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.

[petter]

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.

[Kushal_Chawda]

Thanks for the explanation