How to filer on subtotal with between values?

Steven Sun shared this question 2 months ago
Answered

Hi Team,

I've made a subtotal for number of how many the BRN repeats, and I need to filter on this number but in vain.

Any idea about this? thanks in advance.

Comments (12)

photo
1

hi Steven,

Thanks for reaching out to support with your question. I attempted a quick replication and can see what you mean, the "count" function can be a little tough to work with in the report builder. I'm not super literate in SQL, but there might be a way to make a calculated field in freehand SQL that performs this calculation as desired. I've reached out to a content creation specialist to see if they can help devise a solution here. Will let you know what we come up with.

Thanks,

Eric

photo
1

Hi Steven,

Just trying to get a proper replication going here - a little foggy on how the "subtotal" is set up in your environment.

In our lab, we have configured a subtotal of "sum units sold" based on Product name in this case -


79beec341c46f04cb850d4f379fe0c72

However your screenshots show a separate column as opposed to a subtotal row - , perhaps you could expand on this? Maybe it's a calculated field or something.

Regardless, in this case, the goal would be to filter by this subtotal value (36, 26, 15, etc)? In which case, I think I'm starting to understand the configuration here... I don't know how I would filter on a subtotal value like that, it may require a code change to do it within the GUI. I feel like for now, freehand SQL or a calculated field is going to be the way to achieve this.

That said, type of assistance is normally facilitated by our consulting team; is there a team member you have worked with previously when building content?

Thanks,

Eric

photo
1

Hi Eric,

Thanks for this quick response!

What you understand is correct, I need to filter on that numbers of subtotal. I'm not quite good at SQL, so maybe you can advise further how to do it in freehand SQL.

Thanks a lot!

photo
1

Hi Steven,

I think it'd be as simple as a making a calculated field similar to this example -

https://stackoverflow.com/questions/12927268/sum-of-grouped-count-in-sql-query

Hope this helps!

Thanks,

Eric

photo
1

Hi Steven,

Just wanted to check in to make sure you had what you were looking for here?

Thanks,

Eric

photo
1

Hi Eric,

Thanks for the skill 'SUM of grouped COUNT in SQL Query' sharing with me.I have no time yet to implement this into our application yet.

I'll try to do this soon later.

Thanks again.

photo
1

Hi Eric,

The skill shared from the webpage cannot help since I have more than 2 columns, still studying.

photo
1

Hi Steven,

Thanks for the update, our region's consultant is currently on vacation so I'm unable to ping him on this at this time, but I may be able to get an answer in the meantime. If you could, let me know how it goes on your end.

Thanks,

Eric

photo
1

Hi Eric,

Thank you for coming back for me.

Another new month arrived, the user required again for this function while I have not got any progress yet.

Can any free hand SQL or Calculated field help on this?

Thanks again.

photo
1

Hi Steven,


I'm just jumping on this one for Eric while he is out sick.


I think this is a relatively easy solution, using subqueries!


What you'll want to do is create a Master Query with the BRN column pulled in twice, once for the BRN number, and once for the Count BRN number. You will then need to create an append subquery and pull in the rest of the columns in the Report. This will result in the number 2 repeated twice for the first two columns, and 3 repeated three times for the remaining columns. You should then be able to filter on Count(BRN).


Let me know how this goes.


Kind regards,

Simon

photo
1

Hi Steven,


I just wanted to check in to see how you are travelling with this question. Please let me know if you need further assistance, otherwise, I will go ahead and mark this ticket as completed.


Kind regards,

Simon

photo
1

Hi Steven,


I'm just messaging to let you know that I will be marking this question as completed for the time-being. Please feel free to re-open this ticket by responding here and I will get back to you shortly.


Kind regards,

Simon