How to ignore hidden fields in group by?

Machiel Treffers shared this question 3 years ago
Answered

Hi all,

At the moment I am trying to build a report containing one sub query. Let's say the main query contains a metric "Qty. items received" and the sub query contains a metric "Qty. items put on stock". I'd like to sum both metrics per hour, because I am interested in how many of the items I have received and how many have already put on stock.

Because I am only interested in items received in a specific way, I added a filter to my Master query to just get those items. I also left outer joined both queries on "Item code".

To achieve this, I add "Item code" to both queries and to the join condition. After that I hide both columns "Item code" as I do not want to display this field. The problem which arises here, is that "Item code" is now added to the GROUP BY clause of the query, and my report is no longer displaying a sum of "Items received" and "Items sold" per hour, but per hour and item code... which is not what I am looking for.

Is there a way to have a join between two sub queries, of which the columns (that are part of the join condition) are ignored in the group by?

Thanks!

Best regards,

Machiel

Replies (1)

photo
1

Hi Simon,

Sorry for my late reply. Thank you for your suggestion. I managed to solve my issue in the Yellowfin view, but I will also have a look at your proposed solution to see if it can help us out with future cases.

Kind regards,

Machiel

Leave a Comment
 
Attach a file