Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Everyone,
Hopefully everyone is staying safe and healthy. I'm trying to create a calculated dimension based on sales for a customer. Here is the calculation I am using for the dimension, but I have something messed up. I'm thinking I need an AGGR, but not too sure. Thanks
If(Sum({<d365prd_salesorderdetail.davey_sold = {1},[d365prd_opportunity.davey_renewalyear]= {$(=$(vFisYr0))}>}d365prd_salesorderdetail.extendedamount) > 0 and
Sum({<d365prd_salesorderdetail.davey_sold = {1},[d365prd_opportunity.davey_renewalyear]= {$(=$(vFisYr1))}>}d365prd_salesorderdetail.extendedamount) > 0,'qmem://<bundled>/BuiltIn/check_g.png','qmem://<bundled>/BuiltIn/cross_r.png')
maybe this:
=Aggr( If(
Sum( {< d365prd_salesorderdetail.davey_sold = {1}, [d365prd_opportunity.davey_renewalyear] = {$(=$(vFisYr0))} >}
d365prd_salesorderdetail.extendedamount ) > 0
and
Sum( {< d365prd_salesorderdetail.davey_sold = {1}, [d365prd_opportunity.davey_renewalyear] = {$(=$(vFisYr1))} >}
d365prd_salesorderdetail.extendedamount ) > 0,
'qmem://<bundled>/BuiltIn/check_g.png',
'qmem://<bundled>/BuiltIn/cross_r.png'
), customer )
I hope it can helps.
Worked great, but forgot about checking for both to be equal to zero.....
=Aggr(If(Sum( {< d365prd_salesorderdetail.davey_sold = {1}, [d365prd_opportunity.davey_renewalyear] = {$(=$(vFisYr0))} >} d365prd_salesorderdetail.extendedamount ) > 0 and
Sum( {< d365prd_salesorderdetail.davey_sold = {1}, [d365prd_opportunity.davey_renewalyear] = {$(=$(vFisYr1))} >} d365prd_salesorderdetail.extendedamount ) > 0,'qmem://<bundled>/BuiltIn/led_g.png',
(If(Sum( {< d365prd_salesorderdetail.davey_sold = {1}, [d365prd_opportunity.davey_renewalyear] = {$(=$(vFisYr0))} >} d365prd_salesorderdetail.extendedamount ) = 0 and
Sum( {< d365prd_salesorderdetail.davey_sold = {1}, [d365prd_opportunity.davey_renewalyear] = {$(=$(vFisYr1))} >} d365prd_salesorderdetail.extendedamount ) = 0,'qmem://<bundled>/BuiltIn/led_r.png','qmem://<bundled>/BuiltIn/led_y.png',Ship_mca_sapaccountid ))))
please mark an answer as solution if applicable.
The first solution worked, however I forgot to check if both fields are equal to zero. See the post below your solution. For some reason it does not work. Thanks for your help.
The first response worked, but I need to add another expression and when I did it stopped working.