An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING cla
Hi guys, I have a problem with Report Filters. I have two calculated fields (1 & 2 below). Another calculated field (3) is also present, using the input from previous calculated fields.
I want to use this last calculated field (3) as a filter as well. When I try to enter a value to the filter, I receive below error:
An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference.
Calculated field 1: CASE WHEN SUM ( Aantal ) != 0 THEN SUM ( Opbrengst) / SUM ( Aantal) ELSE 0 END
Calculated field 2: CASE WHEN SUM ( Aantal_LY ) != 0 THEN SUM ( Opbrengst_LY ) / SUM ( Aantal_LY) ELSE 0 END
Calculated Field 3: CASE WHEN Calculated field 2 != 0 THEN ( Calculated field 1 - Calculated field 2 ) / Calculated field 2 * 100 ELSE 0 END
Apperantly, it needs to be a "Having" clause instead of "where" when there is an aggregation. But Yellowfin does not convert this "where" to "having" automatically.
Any ideas on how to solve this?
The same question
Hey Vefa,
I hope you are well!
I have a feeling this is due to the aggregate, usually Yellowfin will always use WHERE clause, though I believe I read somewhere, that when using the aggregate, this forces it into HAVING - Im not entirely sure why, So I will double check this and confirm with you shortly! :)
Best Wishes,
Lesley
Hey Vefa,
I hope you are well!
I have a feeling this is due to the aggregate, usually Yellowfin will always use WHERE clause, though I believe I read somewhere, that when using the aggregate, this forces it into HAVING - Im not entirely sure why, So I will double check this and confirm with you shortly! :)
Best Wishes,
Lesley
Hi Vefa,
Just to let you know, I have just found out - Brett is on annual leave until Tuesday next week! I understand this is a priority for you, however, he does have availabilities on Tuesday 7th, Wednesday 8th and Thursday 9th next week! Please let me know which day suits you best so I can arrange it!
Thank you & Best Wishes,
Lesley
Hi Vefa,
Just to let you know, I have just found out - Brett is on annual leave until Tuesday next week! I understand this is a priority for you, however, he does have availabilities on Tuesday 7th, Wednesday 8th and Thursday 9th next week! Please let me know which day suits you best so I can arrange it!
Thank you & Best Wishes,
Lesley
Tuesday in the afternoon would be the best for me. If this is not possible, then Wednesday in the morning please.
Thanks!
Tuesday in the afternoon would be the best for me. If this is not possible, then Wednesday in the morning please.
Thanks!
Hey Vefa,
I hope you are well!
I will confirm a time for you for either Tuesday afternoon or Wednesday morning, by the end of today! :)
Best Wishes,
Lesley
Hey Vefa,
I hope you are well!
I will confirm a time for you for either Tuesday afternoon or Wednesday morning, by the end of today! :)
Best Wishes,
Lesley
Hey Vefa,
Just to keep you in the loop with whats going on - I reached out to the CSM and he has requested a time on Wednesday morning with Brett for a screen share - however, I have yet to receive a confirmation. So I will update you as soon as we get one! :)
Best Wishes,
Lesley
Hey Vefa,
Just to keep you in the loop with whats going on - I reached out to the CSM and he has requested a time on Wednesday morning with Brett for a screen share - however, I have yet to receive a confirmation. So I will update you as soon as we get one! :)
Best Wishes,
Lesley
Hey Vefa,
I hope you are well!
Just to let you know, I chased the CSM to see if there was a confirmation for the Wednesday morning call, unfortunately, there has not yet been a response!
As Brett will not be back till Tuesday, I expect we wont find out till then! So if you could keep your Wednesday morning free, so I can get that call booked in with him first thing and then I can confirm with you on Tuesday if we can go ahead with the call, the following morning.
Let me know if this is ok?
Best Wishes,
Lesley
Hey Vefa,
I hope you are well!
Just to let you know, I chased the CSM to see if there was a confirmation for the Wednesday morning call, unfortunately, there has not yet been a response!
As Brett will not be back till Tuesday, I expect we wont find out till then! So if you could keep your Wednesday morning free, so I can get that call booked in with him first thing and then I can confirm with you on Tuesday if we can go ahead with the call, the following morning.
Let me know if this is ok?
Best Wishes,
Lesley
Hey Lesley, Wednesday morning is fine, thanks!
Hey Lesley, Wednesday morning is fine, thanks!
Hey Vefa,
Hope you had a lovely weekend!
Just another update - I will be able to confirm with you the exact time/ availability of our consultant tomorrow morning!
Best Wishes,
Lesley
Hey Vefa,
Hope you had a lovely weekend!
Just another update - I will be able to confirm with you the exact time/ availability of our consultant tomorrow morning!
Best Wishes,
Lesley
Hey Vefa,
I hope you are well!
Good news and bad news!
It turns out, Brett is a very sought after consultant and unfortunately, his time tomorrow morning has already been booked up!
However, the good news, I did manage to block out a time for 1:30pm UK time tomorrow (Wednesday). I understand this is not what you would have preferred, however, this is the only time I could get for Brett. I will be sending you a ring central meeting invite via email & also providing the details within this ticket. (please see below for ring central meeting details)
Please let me know if this is ok. If you need this to be rearranged it may have to wait till next week :(
But let me know as soon as you can so I can see what I can do! :)
Best Wishes,
Lesley
Hi there,
Lesley-Anne Goggin is inviting you to a scheduled RingCentral meeting.
Topic: Lesley-Anne Goggin's RingCentral Meeting
Time: Jan 8, 2020 1:30 AM London
Join from PC, Mac, Linux, iOS or Android: https://meetings.ringcentral.com/j/1497178263
Or iPhone one-tap :
US: +1(773)2319226,,1497178263#
AU: +61283108040,,1497178263#
GB: +442038754507,,1497178263#
JP: +81645602947,,1497178263#
Or Telephone:
Dial(for higher quality, dial a number based on your current location):
US: +1(773)2319226
AU: +61283108040
GB: +442038754507
JP: +81645602947
Meeting ID: 149 717 8263
International numbers available: https://meetings.ringcentral.com/teleconference
Hey Vefa,
I hope you are well!
Good news and bad news!
It turns out, Brett is a very sought after consultant and unfortunately, his time tomorrow morning has already been booked up!
However, the good news, I did manage to block out a time for 1:30pm UK time tomorrow (Wednesday). I understand this is not what you would have preferred, however, this is the only time I could get for Brett. I will be sending you a ring central meeting invite via email & also providing the details within this ticket. (please see below for ring central meeting details)
Please let me know if this is ok. If you need this to be rearranged it may have to wait till next week :(
But let me know as soon as you can so I can see what I can do! :)
Best Wishes,
Lesley
Hi there,
Lesley-Anne Goggin is inviting you to a scheduled RingCentral meeting.
Topic: Lesley-Anne Goggin's RingCentral Meeting
Time: Jan 8, 2020 1:30 AM London
Join from PC, Mac, Linux, iOS or Android: https://meetings.ringcentral.com/j/1497178263
Or iPhone one-tap :
US: +1(773)2319226,,1497178263#
AU: +61283108040,,1497178263#
GB: +442038754507,,1497178263#
JP: +81645602947,,1497178263#
Or Telephone:
Dial(for higher quality, dial a number based on your current location):
US: +1(773)2319226
AU: +61283108040
GB: +442038754507
JP: +81645602947
Meeting ID: 149 717 8263
International numbers available: https://meetings.ringcentral.com/teleconference
Thanks a lot Lesley, I accepted the invitation.
Thanks a lot Lesley, I accepted the invitation.
Great! Thanks for confirming, Vefa! :)
Look forward to speaking with you tomorrow - I may join the call to, mainly to listen in, so I can learn more about this too! :)
Best Wishes,
Lesley
Great! Thanks for confirming, Vefa! :)
Look forward to speaking with you tomorrow - I may join the call to, mainly to listen in, so I can learn more about this too! :)
Best Wishes,
Lesley
Hey Vefa,
Thank you for joining the call! :)
After the call, Brett and myself (mostly Brett if I am honest) replicated your issue. Although Brett was able to replicate this, we will still be reaching out to the developer team in Australia to ask if it is possible to have a calculated field filter based on 2 calculated fields.
Workaround option
Ok so Brett did manage to do a test which we are hoping you will be able to try out (and fingers crossed it works for you!) as it worked for Brett.
If you can apply the logic of the 3 calculations into 1 calculated field, So you will still be able to have your 1st and 2nd calculated fields, but rather than basing the 3rd calculated field on the 1st and 2nd, you will apply the logic of all three into the 3rd so it is not filtering from the calculated fields.
I hope this makes sense! Let me know if it doesn't and if you need more explanation! :)
Thanks & Best Wishes,
Lesley
Hey Vefa,
Thank you for joining the call! :)
After the call, Brett and myself (mostly Brett if I am honest) replicated your issue. Although Brett was able to replicate this, we will still be reaching out to the developer team in Australia to ask if it is possible to have a calculated field filter based on 2 calculated fields.
Workaround option
Ok so Brett did manage to do a test which we are hoping you will be able to try out (and fingers crossed it works for you!) as it worked for Brett.
If you can apply the logic of the 3 calculations into 1 calculated field, So you will still be able to have your 1st and 2nd calculated fields, but rather than basing the 3rd calculated field on the 1st and 2nd, you will apply the logic of all three into the 3rd so it is not filtering from the calculated fields.
I hope this makes sense! Let me know if it doesn't and if you need more explanation! :)
Thanks & Best Wishes,
Lesley
Hey Lesley, YES, workaround solution works for me as well!
Thanks for the tip!
I will use this solution! You can close the ticket for now if you want, my problem is solved!
Regards to Brett!
Hey Lesley, YES, workaround solution works for me as well!
Thanks for the tip!
I will use this solution! You can close the ticket for now if you want, my problem is solved!
Regards to Brett!
Hey Vefa,
That is great news! Very pleased you got this working :)
I have informed Brett and he is also happy to hear this!
I have still sent an email over to the Australian team to find out about filter limitations on calculated fields based on calculated fields.
I will go ahead and close this ticket per your request, however, if I get a response from the team, I will likely add their response to this ticket while keeping it closed. You will not need to respond. Although, if you have any further questions, do let me know! I am happy to help :)
Best wishes,
Lesley
Hey Vefa,
That is great news! Very pleased you got this working :)
I have informed Brett and he is also happy to hear this!
I have still sent an email over to the Australian team to find out about filter limitations on calculated fields based on calculated fields.
I will go ahead and close this ticket per your request, however, if I get a response from the team, I will likely add their response to this ticket while keeping it closed. You will not need to respond. Although, if you have any further questions, do let me know! I am happy to help :)
Best wishes,
Lesley
Replies have been locked on this page!