Zendesk Explore: Need to set value of column based on condition
Hi All,
I am facing difficulty in setting up a report.
In a ticket, we have amount1, amount2, amount of refund.
We have 1 more field refund type, it has 2 values partial and full.
If the refund type is full agent enters the amount in "amount of refund".  No problem easy to do.
If the refund type is "Partial" the agent needs to enter values in amount1, amount2 fields.
So, my problem is when I generating report in table format, if refund type is = Partial I need to do sum of amount1 and amount2 and want to display it as amount of refund. Is there any way to do it?

Mrunal
If you have your custom fields returned as metrics in your report, you can add the Amount1 and Amount2 values together under Result Manipulation> Result Metric Calculation.
See the details here.

Hello Graeme Carmichael,
Thank you for pointing me towards some solution. I had a look at the article and did some stuff.
I have added all metrics required for addition.
We have the following metrics:
SAP: Amount Number1, SAP: Amount Number2, SAP: Amount Number3, SAP: Amount of refund
So, when the refund type is full I want to show SAP: Amount of refund value in Metric calculations, and when refund type is partial I want to show the addition of amount number 1,2,3.
For identifying partial and full refund I have created one more metric YesNoRefundAmount which return 1 if the type is full and 0 if it's partial. This metric is working fine but Result Metric calculation is not working fine.
I have created a result metric calculation (Name: Metric calculations) last column of the report. THere you can see addition is working fine but SAP: Amount of refund is not reflecting.
Here is a small snippet I wrote for it:
IF (SUM(YesNoRefundAmount)) THEN
SUM(SAP: Amount of refund(AGT))
ELSE
SUM(SAP: Amount Number 1(AGT))+SUM(SAP: Amount Number 2(AGT))+SUM(SAP: Amount Number 3(AGT))
ENDIF
Can you help me identify where I am going wrong? 
Mrunal
You will need to make the start of your IF statement return true or false.
For example,
IF SUM(YesNoRefundAmount) = 0 THEN
SUM(SAP: Amount of refund(AGT))
ELSE
SUM(SAP: Amount Number 1(AGT))+SUM(SAP: Amount Number 2(AGT))+SUM(SAP: Amount Number 3(AGT))
ENDIF

Hi Graeme Carmichael,
No luck so far..
Formula:
IF (SUM(YesNoRefundAmount)=0) THEN
SUM(SAP: Amount Number 1(AGT))+SUM(SAP: Amount Number 2(AGT))+SUM(SAP: Amount Number 3(AGT))
ELSE
SUM(SAP: Amount of refund(AGT))
ENDIF
Output:When I changed the formula to output 1 instead of SUM(SAP: Amount of refund(AGT))
IF (SUM(YesNoRefundAmount)=0) THEN
SUM(SAP: Amount Number 1(AGT))+SUM(SAP: Amount Number 2(AGT))+SUM(SAP: Amount Number 3(AGT))
ELSE
1
ENDIF
So it returned 1 successfully but not returning the value of SUM(SAP: Amount of refund(AGT)) this is strange. All the amount fields have a decimal datatype. 
Mrunal
Try this:
Looks like you have already created a custom attribute that shows under rows to display Full or Partial for each ticket. That is under Calculations>Attributes> Standard Calculated Attribute.
For your refunds, under Calculations, create two new Standard Calculated metrics. One for 'Full Refund Amount' and one for 'Partial Refund Amount'.
Then under Calculations, create one more Standard Calculated Metric:
IF [SAP Refund Type] = "Full" then FullRefundAmount ELSE PartialRefundAmount END
Please sign in to leave a comment.
5 Comments