https://netsuiteprofessionals.com logo
Join Slack
Powered by
case when criteria1 (increase) then 1 else 0 end +...
# suiteanalytics
k
case when criteria1 (increase) then 1 else 0 end + case when criteria 2 (decrease) then 1 else 0 end with a goal of 2
g
Two questions - would I do that as a summary criteria, and are you applying addition between the two or was that just a faster way to type 'and'?
k
addition between the two - and yes summary criteria
essentially - if it's an increase give me a 1, if it's a decrease in the other give me a 1. I only want it if both have a 1.
g
Gotcha. Not getting expected results. I'll keep tinkering.
k
can you elaborate a bit on your formula, and use case?
g
It started as a YOY comparison for Print and Digital sales for our customers. Have a case statement for 2018 $$, 2019 $$, and a YOY $$. I have a custom segment on our items that classifies them as Print, Digital or a few others. But for this saved search, criteria is set to only look at print and digital.
message has been deleted
This is the criteria i was able to apply to show only those customers with a YOY decrease in Print OR a YOY increase in digital. But, we want a more granular look to see those customers that have both, to see if $$ is migrating from one medium to other.
k
Gotcha
My idea should work then. Can you share the screenshot you tried?
Wondering if maybe you missed something
Oh wait. You have this on your criteria without expression builder turned on
Try turning it on and make sure the value between says or
I mean and not or
g
I have it on OR right now, even if it's not showing (odd netsuite behavior) when I change it to AND , no results show up.
So now this is returning results, but not the right ones.
message has been deleted
k
any way you can copy paste that text here?
kinda hard for me to absorb looking at it in image form!
g
CASE WHEN(CASE WHEN {item.custitem_sm_year} = '2019' AND {item.custitem_cseg_sm_media_type} = 'Digital' THEN {amount} ELSE 0 END) - (CASE WHEN {item.custitem_sm_year} = '2018' AND {item.custitem_cseg_sm_media_type} = 'Digital' THEN {amount} ELSE 0 END) >0 THEN 1 ELSE 0 END + CASE WHEN(CASE WHEN {item.custitem_sm_year} = '2019' AND {item.custitem_cseg_sm_media_type} = 'Print' THEN {amount} ELSE 0 END) - (CASE WHEN {item.custitem_sm_year} = '2018' AND {item.custitem_cseg_sm_media_type} = 'Print' THEN {amount} ELSE 0 END)<0 THEN 1 ELSE 0 END
Sorry about that, and thanks for looking at it!
k
Your parenthesis placement was JUUUST off
Copy code
CASE WHEN(
CASE WHEN {item.custitem_sm_year} = '2019' AND {item.custitem_cseg_sm_media_type} = 'Digital' THEN {amount} ELSE 0 END - 
(CASE WHEN {item.custitem_sm_year} = '2018' AND {item.custitem_cseg_sm_media_type} = 'Digital' THEN {amount} ELSE 0 END)) >0 THEN 1 ELSE 0 END 
+ 
CASE WHEN(CASE WHEN {item.custitem_sm_year} = '2019' AND {item.custitem_cseg_sm_media_type} = 'Print' THEN {amount} ELSE 0 END - (CASE WHEN {item.custitem_sm_year} = '2018' AND {item.custitem_cseg_sm_media_type} = 'Print' THEN {amount} ELSE 0 END))<0 THEN 1 ELSE 0 END
g
Hmmm, same results come through as the formula i pasted in.
k
Do you have a couple specific customers you had validated that this search should pick up?
Oh i think I know your problem.
hold on
g
Holding.
k
Your summary type will be "Max" your goal is still 2.
Copy code
CASE WHEN (
sum(CASE WHEN {item.custitem_sm_year} = '2019' AND {item.custitem_cseg_sm_media_type} = 'Digital' THEN {amount} ELSE 0 END) - 
sum(CASE WHEN {item.custitem_sm_year} = '2018' AND {item.custitem_cseg_sm_media_type} = 'Digital' THEN {amount} ELSE 0 END)
) >0 THEN 1 ELSE 0 END 

+ 
CASE WHEN (
sum(CASE WHEN {item.custitem_sm_year} = '2019' AND {item.custitem_cseg_sm_media_type} = 'Print' THEN {amount} ELSE 0 END) - 
sum(CASE WHEN {item.custitem_sm_year} = '2018' AND {item.custitem_cseg_sm_media_type} = 'Print' THEN {amount} ELSE 0 END)
)<0 THEN 1 ELSE 0 END
that might evaluate to unexpected error
but your summary levels are all the same here.
So I think it shouldn't
Essentially - "Add up all my 2019" and then "Add up all my 2018" and subtract it from 2019. Is that greater than 0, give me 1, otherwise give me a 0. the same in reverse for the other category.
Now - Joining to your item record seems kinda odd to me.
g
Let me see.
No results.
k
but if you did that perfectly and no one misused anything then you are probably ok
Are you sure that it should get results?
g
Oh, because of 2018 and 2019 on the item? Yeah...that's a pain for me always.
Yup. I'm sure. But if i have to do it manually, with the earlier OR expression, then that's what it will have to be.
k
is it giving you unexpected error?
or just no results?
g
No results - so the formula isn't bunk
k
Try adding that as a results column
and see if you get any twos there
instead of adding it to your criteria.
Might be something off going on there.
Maybe need to insert a couple NVLs
g
OOOOOOOOOOO damn. I think it's because it's grouped by the Print/Digital field on the item? No 2s...just 1s and 0s
k
That'll gank you.
Try ungrouping that field.
also ungroup by year
which means you might need to add additional columns for 2018 print, 2019 print, 2018 digital 2019 digital
so you can have those in the report
g
Bingo - now 2s are showing up. And they seem to be for the clients that are meet the criteria.
Yeah, was thinking i'd have to add more columns to handle not being grouped.
Well, sir - you have been a HUGE help! This is great, thank you so much!
This is definitely workable now!
k
You could also get fancy with decode - so that you don't have to update it next year.
g
That's the plan for a lot of my saved searches - since i have to do YOY based on a field on the item, and not the actual calendar. Too many to manually update every year
2 Views
Open in Slack
PreviousNext
Powered by