Hi all
First time posting and currently work fir a homecare company... but if you see attached, i am trying ti write a query calculation but keep getting a parsing error but i cant seem to resolve.. any thought would be appreciated!!!!!(https://uploads.tapatalk-cdn.com/20170413/e97a9ed993883d3fa6cf576241cb2983.jpg)
Thanks in advance!!
Sent from my SM-G925F using Tapatalk
I've got a sore neck now...
1. What parsing error do you get?
2. Have you tried the count function as a separate field to make sure that works for you? I haven't use a WHEN clause in that sort of thing before, but that might not mean anything.
3. Do you need another ) before the Else clause?
Sorry for the picture
Error code: QE-DEF-0260
No i havnt i have tried seperatly and same error, notsure how i can word/replace the word 'when'
Dont believe need more or less brackets seems to me for every one i open i have then closed :s
Sent from my SM-G925F using Tapatalk
Can you copy/paste the expression in your post?
I don't think the "when" clause is valid with an "if" statement. You'll need to use a case statement instead of "if" or stick with "if" but consider using "and" to combine multiple conditions.
It is difficult to offer a specific suggestion unless you can post the actual expression.
When I need to use a summary function in a conditional manner, I find it works best to put the condition inside the function rather than the function inside the condition.
count ( distinct case when {some condition is true} then [Your query item] else null end )
You might also consider whether order required delivery date is a date or a date/time data type. You are comparing to a date/time literal which may not be valid if the field only holds a date. Of course hard coding the date/times seems a bit rigid unless you will never need to worry about anything that happened after March 31st.
I will at somepoint write the actuall expression and what i am trying to achieve by tuesday latest and hope you can offer more of a suggestion then if thats ok lynn.. appreciate your thoughts... i will place a cast function on order required delivery date to make all fields be same format as in date only and will be back in touch!
Sent from my SM-G925F using Tapatalk
So my expression i currently have :-
If([order required delivery date] between 2016-10-01T00:00:00.000 and 2017-03-31T00:00:00.000)
THEN
(Count(distinct([order crm number]) when [order status] in ('completed', 'received', 'writtenoff', 'awaitingpick', 'awaitingitemlabels', 'awaitingdel', 'awaitingdelconf', 'awaiting check', 'awaitingpickinglists')
ELSE
(0)
All i ak trying to is between them dates count hiw many orders within them order status we have and do a count on distict order crm value...
Novice still at using cignos as started to use 5 months ago with no training from company so sorry for simple errors
Appreciate any help
Thank you
Sent from my SM-G925F using Tapatalk
Quote from: imzy66 on 18 Apr 2017 04:20:49 AM
So my expression i currently have :-
If([order required delivery date] between 2016-10-01T00:00:00.000 and 2017-03-31T00:00:00.000)
THEN
(Count(distinct([order crm number]) when [order status] in ('completed', 'received', 'writtenoff', 'awaitingpick', 'awaitingitemlabels', 'awaitingdel', 'awaitingdelconf', 'awaiting check', 'awaitingpickinglists')
ELSE
(0)
All i ak trying to is between them dates count hiw many orders within them order status we have and do a count on distict order crm value...
Novice still at using cignos as started to use 5 months ago with no training from company so sorry for simple errors
Appreciate any help
Thank you
Sent from my SM-G925F using Tapatalk
How about:
If([order required delivery date] between 2016-10-01T00:00:00.000 and 2017-03-31T00:00:00.000 and [order status] in ('completed', 'received', 'writtenoff', 'awaitingpick', 'awaitingitemlabels', 'awaitingdel', 'awaitingdelconf', 'awaiting check', 'awaitingpickinglists'))
THEN
(Count(distinct [order crm number]))
ELSE
(0)
Quote from: imzy66 on 18 Apr 2017 04:20:49 AM
So my expression i currently have :-
If([order required delivery date] between 2016-10-01T00:00:00.000 and 2017-03-31T00:00:00.000)
THEN
(Count(distinct([order crm number]) when [order status] in ('completed', 'received', 'writtenoff', 'awaitingpick', 'awaitingitemlabels', 'awaitingdel', 'awaitingdelconf', 'awaiting check', 'awaitingpickinglists')
ELSE
(0)
All i ak trying to is between them dates count hiw many orders within them order status we have and do a count on distict order crm value...
Novice still at using cignos as started to use 5 months ago with no training from company so sorry for simple errors
Appreciate any help
Thank you
Sent from my SM-G925F using Tapatalk
Both BigChris and I provided some suggestions. Which of those things have you tried already?
What I find works best is what I previously suggested:
count ( distinct case when {some condition is true} then [Your query item] else null end )
This will return either the thing you want to count or null. The count function will ignore nulls.
Count(distinct
case
when [order status] in ('completed', 'received', 'writtenoff', 'awaitingpick', 'awaitingitemlabels', 'awaitingdel', 'awaitingdelconf', 'awaiting check', 'awaitingpickinglists' )
and [order required delivery date] between 2016-10-01T00:00:00.000 and 2017-03-31T00:00:00.000
then [order crm number]
else null
end
)
Simple and perfect, just re-arranging equation made this work! Thank you
Sent from my SM-G925F using Tapatalk
I dine as mfgf and all seems to be fine just testing results so will keep you posted
Sent from my SM-G925F using Tapatalk
Quote from: imzy66 on 18 Apr 2017 04:36:55 AM
I dine as mfgf and all seems to be fine just testing results so will keep you posted
Sent from my SM-G925F using Tapatalk
I think Lynn's solution is probably more efficient, and it's certainly more elegant :)
MF.
QuoteI think Lynn's solution is probably more efficient, and it's certainly more elegant :)
Generally the case I find...
So i have created the query calculation with in the query i then checked repirt validation and all seems fine but as soon as i put the data item (query calculation) im getfing the following error:-
Uda-ee-0094 the operation "greater_equal"is invalid for the following combination of data types"date2" and "character"
So the only other thing i am trying to diplay with the calulation is names
Sent from my SM-G925F using Tapatalk
Quote from: imzy66 on 18 Apr 2017 05:06:12 AM
So i have created the query calculation with in the query i then checked repirt validation and all seems fine but as soon as i put the data item (query calculation) im getfing the following error:-
Uda-ee-0094 the operation "greater_equal"is invalid for the following combination of data types"date2" and "character"
So the only other thing i am trying to diplay with the calulation is names
Sent from my SM-G925F using Tapatalk
I'd be checking what data type [order required delivery date] is defined as in your database / model.
MF.
Update, used cast function on order required delivery date and report now working fine! Thank you for your help!
Sent from my SM-G925F using Tapatalk