MIN function refuses to calculate when each parameter is a formula rather than a single field?

[Jake_Raz]

Hello! I have the following formula that I keep on getting an error message on, and I'm not sure why:

MIN(
DDIFF([Days in Invoice Billing End Date],[Days in Invoice Line Item Service Date]),
DDIFF([Days in Invoice Line Item Service Date],[Days in Invoice Billing Start Date])
)

This results in an error. However, each of those DDIFF expressions work fine when on their own. For example (the last two columns correspond to the individual DDIFF formulas):

However, in some cases, the first one is negative, and in other cases the latter is negative. I want to create a combined formula that shows me the lowest negative between the two of them. For example, consider these two lines:

I would want this new combined formula to just show "-792" and "-41", respectively, since those are the lowest values in each row between the two DDIFF columns.

I thought "Oh! I can use the MIN formula!" Unfortunately, when I try to use this formula:

The formula window won't accept it, and shows this error at the bottom of the window:

Function Syntax Error:
Error in function definition (Min): Expecting parameter of type 'Set Expression' but found 'Numeric Expression'.

Can someone explain to me why on Earth this would ever make sense?

Like, don't get me wrong...I think I understand what it's saying: it's basically assuming that I should only be giving it two number fields directly (i.e. a "Set Expression" meaning "this data item represents the set of all values in a particular number field"), and it would just compare those two. If this is accurate, however, this would mean that, essentially, the MIN formula cannot compare two arbitrary numbers to each other, but instead can only directly compare field values to each other. Is this true? If so, does this truly mean I can't use it to compare the results of two separate formulas (since they're the results of formulas and don't actually exist in the eCube anywhere)?

To back up a bit: I'm trying to analyze invoice data. Invoices have a "Billing Start Date" and a "Billing End Date". However, Invoices also have multiple Line Items associated with them, and each Line Item has it's own "Service Date". The Services Dates are supposed to always be within the date range of the Billing Start and End dates at the top of the invoice, but in the real-world this isn't always the case. I'm trying to identify all instances of a Line Item where the Service Date is outside the Billing date range (set by the Billing Start and Billing End defined on the parent Invoice for the Line Item). The way I solved this was by using the DDIFF formulas for each side of the problem: show me all instances where the Service Date is before the Billing Start (i.e. if you subtract the Billing Start from the Service Date and get a negative number, that means the Service Date must be prior to the Billing Start), and then separately, show me all instances where the Service Date is after the Billing END (i.e. do the opposite of the above, but reverse the order: subtract the Service Date from the Billing End, and if it's negative, then you know the Service Date must be after the End Date).

So far so good! I can create a combined formula that outputs a "1" if either situation is true (i.e. the Service Date is before the Start OR after the End), and filter on that. However, what I'm trying to figure out is: how far out of the date range is it? Reason being, I want to be able to filter out any "near misses" (where it's only a day or two outside the range). To do this I need to compare both those date calculations and take the larger one, or in this case, the smaller one since they're negative numbers. Thus why I was trying to use a MIN formula to compare those two DDIFF's. However, I'm open to alternative suggestions!

Please note: I don't have access to modify anything in the 'backend', I'm only limited to what I can do in the front-end UI (i.e. with formulas and/or scripts). Also, we're on an older version of Sisense, if that makes a difference: Version 8.2.1.10110 (Windows, not Linux).

[HamzaJ · Accepted solution]

Hey @Jake_Raz ,l

The min-function does not work like that. You can only offer it 1 measurement. You could use an group by aggregation, however this means you need to add a dimension (or atleast not a calculation of a field). Like min(days by date, DDIFF([Days in Invoice Billing End Date],[Days in Invoice Line Item Service Date])) will generate the minimum found value for that date.

If you want to achieve what you are looking for you will need to use a CASE-function. So something like;

CASE
when MIN(DDIFF([Days in Invoice Billing End Date],[Days in Invoice Line Item Service Date])) < 
MIN(DDIFF([Days in Invoice Line Item Service Date],[Days in Invoice Billing Start Date])) then MIN(DDIFF([Days in Invoice Billing End Date],[Days in Invoice Line Item Service Date]))
else MIN(DDIFF([Days in Invoice Line Item Service Date],[Days in Invoice Billing Start Date]))
end

Hamza

[Jake_Raz]

Hey, thanks! I actually went down a similar path earlier, but I forgot to mention it in the original post. I had tried it as an IF formula but it wasn't working so I assumed that wasn't the route to go. However, when I tried your CASE formula, it seems like it's calculating correctly. Thanks!

Here's what I ended up with (I also flipped the sign on the final number so it's easier for users to understand).