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!
I am attaching the solution to this reply.
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:
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.
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
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:
Sum({<mark={$(=concat(distinct chr(39) & GetFieldSelections(mark) & chr(39), ','))},part_no={$(=concat(distinct chr(39) & GetFieldSelections(part_no) & chr(39),','))}>} value)
Expression comments:
Hope this helps,
I am attaching the solution to this reply.
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!
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.
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?
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',
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:
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.
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)
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 👍