calculate by partly filtered value

[BlazkoG]

Hi, I want tu sum 'value' field as below. Filter by 'mark' field., and I want to do this in set analysis.

So i Want to sum by 'part_no', for every part_no that has occurence of filtered 'mark' field.

I could do this in script editor, but there are tens of distinct 'mark' values, and I want to calculate this in dynamic way.

I might filter more than one value.

Thx in advance for help!

[ArnadoSandoval]

Hi @BlazkoG 

I will explain an easy way to implement many SET analysis expressions with the help of QlikSense itself.

Preparing the application:

I created an Excel file with your data, loaded into a brand new QS application, added a table with the same layout of your example, like this:

Implementing fixed Set Analysis

• Select the mark and part_no from the table (You are not in edit mode for these selections)
• Add a KPI control, select Add measure and select the fx option.

• Now, on the right hand side panel (Fields), select the Table containing the Field you want in your KPI, Select the Aggregation function, and make sure to check the Set Expression option, finally click the Insert button (you may explore Set Expressions option while you are here, do not change anything)

• You get the expression below after pressing the Insert button.

Sum({<mark={'X'},part_no={'a'}>} value)

This is a fixed set analysis expression because your current selections are hardcoded in it.

Now let's make the KPI control dynamic, e.g. driven by selections on the UI:

• Let's copy the just created KPI control, and paste it on the same sheet.
• Add a couple of Selectors, one for mark, the second for part_no.
• Adjust the Sheet for these changes.
• It may look like this:

• With the second KPI control, we will edit its expression by implementing the functions: GetFieldSelections and Concat  (Note, you may add a Text control to monitor the GetFieldSelections returned values); this edited expression looks like this:

Sum({<mark={$(=concat(distinct chr(39) & GetFieldSelections(mark) & chr(39), ','))},part_no={$(=concat(distinct chr(39) & GetFieldSelections(part_no)  & chr(39),','))}>} value)

Expression comments:

• When we use functions inside a Set Analysis Expression we include them inside a $(= function )
• The set analysis expression compares the dimension against a SET of values, this set is define between curly brackets, with each value enclosed in single quotes (chr(39)) and separated by commas, like: { 'a', 'b', 'c' }
• The concat function assembles the field selection as expected, enclosing each selection in single quotes, and separating them with commas.
• The GetFieldSelections returns the selection for the supplied field.

Hope this helps,

[ArnadoSandoval · Accepted solution]

@BlazkoG 

I am attaching the solution to this reply.

[BlazkoG]

Hi, OMG that's amazing! Thank You!

I knew the fixed solution in set analysis.

I also managed to  load table: 

With distinct mark as mark1 and part_no. Then i fan filter the mark1. But that does the job partly, because the date table.

Your solution blows my mind, thank You!

[BlazkoG]

Although it seems to work not  as i wanted it to.
I wanted to select one or more values of 'mark' then the formula would check the part_no filtered by "mark' field, then suma value of these part_no throughout the whole table. Do from the original post, i select mark' 'x', and the result i want is 7.

Also, what if I wanted to use data as filter as well. So all the part_no from certain day selected and 'mark' value.

[ArnadoSandoval]

Hi @BlazkoG 

Would you explain how by selecting mark 'X' the expected result is 7 instead of 3, because there are only 3 part numbers with a mark value of 'X'? how is it possible?

[BlazkoG]

Thats why this problem is little tricky. The graphic in the first post explains it. In other words, by selecting 'X' in 'mark', I want to calculate the sum of values of part_no, that have at least one table row with the selection.

By selecting 'Y' the result would be 4 (your data model). As the filtered part_no  is 'b', so I want to sum all the 'b', 

 

[ArnadoSandoval · Accepted solution]

Hi @BlazkoG 

Thank you for extending the explanation of your problem; I will call it, "an indirect selection of the part_no by selecting just the mark field", so when you select the mark 'X', you want to count all the part_no 'a'; I created a new version of the solution previously attached and the following topics helps me to figure out the SET analysis expression: Get Possible Values for a Field  and less than or greater than Date in SET Analysis  as well as the help page for Only - chart function 

Now, the screenshot below is for the new version of the solution:

• The new version of the solution introduced an Alternate State = All_Data for the table on the bottom-left, as it shows all the data excluding the selections and KPIs.
• The Text box above it shows the indirect selections of part_no driven by the selected marks, at the time of the screenshot I selected marks 'X' and 'Z' for part_no 'a' and 'c', the third KPI counts/sum them.
• The date selector works with the fourth KPI, its expression contains the function Only and the selected date is enclosed between double quotes.

These are the new Set Analysis expressions; the first one does not implement date filtering

Sum({<mark=,part_no={$(=concat(distinct chr(39) & part_no & chr(39),','))}>} value)

 the second KPI includes all the part_no since the selected date.

Sum({<date={">=$(=Only(date))"},mark=,part_no={$(=concat(distinct chr(39) & part_no & chr(39),','))}>} value)

Your problem is very interesting, and it is very interesting working in its resolution.

I hope this helps.

[BlazkoG]

Fantastic! Thank You.
Can I ask bonus question? 
I changed the second formula to filter by two fields (no numerical or date). Adding column by 'mark2',
And It works. 

Thanks again!

Sum({<mark=,mark2=,part_no={$(=concat(distinct chr(39) & part_no & chr(39),','))}>} value)

[ArnadoSandoval]

@BlazkoG 

Indeed, the "indirect selection expression works for as many fields the solution handles" you just add the fields to the SET analysis expression !!! I enjoy this problem, it is very enlightening 👍