Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
ksomosera10
Creator II
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
Kushal_Chawda

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

View solution in original post

8 Replies
petter
Partner - Champion III
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.

ksomosera10
Creator II
Creator II
Author

Can I use the primary key as uniqueRowID?

Kushal_Chawda

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

petter
Partner - Champion III
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')

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

Kushal_Chawda

Thanks for the explanation