Hey! Are you staying home? We are too. Still, we prepared a new product release for you!🚀

We hope it comes in handy for your business especially these days. Stay safe & pivot on!

Customize formula depending of the value of a field

Answered

Hi,
I try to customize a report to see if it could be implemented on our project.
But I do not manage to customize simple formula.
I have following fields : 
med_eta that could take 3 values (null, 0 or 1) 
may_pay (country code) : 4 values ‘CIV’,’MDG’,’UGA’ or ‘ZAF’
– med_ide (id) : increment number
 
I would like to calculate % of records in each country with med_eta=1.
How can I implement such a formula in my slice?
 
I have several other questions:How to avoid dividing date into 3 fields?
How to replace the default value of a null field (leave empty instead of blank for an interger, invalid date for a date, etc.)?
How to configure both a Classic and Flat report?
How to use logic operators of formulas?

regards,

2 answers

WebDataRocks Team WebDataRocks January 17, 2020

Hello,
Thank you for your question.
 

  • In order to implement the described logic, the formula has to be specified similar to the following formula:
    "if( ('med_eta') == 1, percent('may_pay'), )"
  • In case the dates should not be divided into three separate subfields, the date string data type has to be used. More information about types can be found following the links:
  • The caption of the null values can be changed both through UI and using the nullValue property of the format object.
    • Using the UI: hover the “Format” button placed on the Toolbar and choose the “Format cells” button in the drop-down list. Change the “Null value” field in the desired way and apply changes.
    • Information about how to change the caption of the null values can be found following the link: Format Object.
  • The configuration specified in the report object will be applied both for the classic and flat forms. In case the functionality you desire to implement is different, we would like to kindly ask you to provide more details about the result you want to achieve.
  • The full list of operators can be found below:
    • + – arithmetic addition operator. Syntax: a + b
    • - – arithmetic subtraction operator. Syntax: a - b
    • * – arithmetic multiplication operator. Syntax: a * b
    • / – arithmetic division operator. Syntax: a / b
    • ^ – arithmetic power operator. Syntax: a^2
    • < – comparison less than operator. Syntax: a < b
    • <= – comparison less than or equal operator. Syntax: a <= b
    • > – comparison greater than operator. Syntax: a > b
    • >= – comparison greater than or equal operator. Syntax: a >= b
    • == – comparison equal operator. Syntax: a == b
    • != – comparison not equal operator. Syntax: a != b
    • or – logical OR operator. Syntax: a or b
    • and – logical AND operator. Syntax: a and b
    • if – conditional operator. Syntax: if(condition, then, else)
    • abs – function that returns the absolute value of a number. Syntax: abs(number)
    • min – function that returns the minimum value. Syntax: min(number1, number2)
    • max – function that returns the maximum value. Syntax: max(number1, number2)
    • isNaN – function that checks whether the value is not a number. Syntax: isNaN(value)
    • !isNaN – function that checks whether the value is a number. Syntax: !isNaN(value)

We hope it helps.
 
Regards,
WebDataRocks Team

WebDataRocks Team WebDataRocks January 31, 2020

Hello,
 
Thank you for your question on email.
 
Our team did not manage to reproduce the described issue on our side.
 
Please note that the input values of dates have to be compliant with ISO 8601 – The International Standard for the representation of dates and time. For example, “2018-01-10” (date) or “2018-01-10T08:14:00” (date and time).
 
You are welcome to see the CodePen we have prepared trying to reproduce the problem.
 
In case that does not help, we suggest modifying the provided CodePen in the way the problem is reproducible.
 
By the way, we would like to kindly ask you to send messages on the forum so we can handle them quickly.
 
Best regards,
WebDataRocks Team

Please login or Register to Submit Answer