Hi! In our system we have Invoice data. There's an Invoice table with various details about the invoice, and each invoice also has multiple line items, tracked on a separate Line Item table. On the Line Item table is a field called "Service Date". I want to find Invoices that contain Line Item Service Dates that are in different months . This is against our billing guidelines, which say that invoices must only contain line items that are within the same calendar month as each other.
I know how to do this in Excel using MAXIF and MINIF formulas to compare the earliest and latest Service Dates within a given Invoice. However, I can't figure out how to do this in Sisense.
Please note: we're on an older Windows-based version of Sisense, v8.2.1.10110. I also do not have access to the ElastiCube configuration, so I cannot create new fields or modify the existing tables. Ideally I'd like to be able to do this within the existing UI in the Sisense front-end with formulas, if possible.
I tried using this formula to render the difference in months as a number, with the idea that I could filter for anything greater than zero: MDIFF(MIN([Months in Invoice Line Item Service Date]),MAX([Months in Invoice Line Item Service Date])) . However, the formula window in Sisense displayed an error and says " Error in function definition (MDiff): Expecting parameter of type 'Dimension Expression' but found 'Numeric Expression'. "
If I remove the MIN/MAX functions, so it's just MDIFF([Months in Invoice Line Item Service Date],[Months in Invoice Line Item Service Date]) , the error message goes away, but then the formula doesn't work properly: it'll just display a "0" for all invoices, even ones that I know should show a 1 or more due to their service dates spanning across multiple months. (Presumably this is because, without the MIN & MAX, it doesn't know which particular Service Dates to compare.)
If I try just subtracting the MIN from the MAX without using the MDIFF function, like MAX([Months in Invoice Line Item Service Date])-MIN([Months in Invoice Line Item Service Date1]) , the formula window lets me submit the formula, but then the whole widget displays a yellow exclamation point error, and the error message says " Query number 0: types tinyint(8,0) and timestamp(7,0) are not equal ".
If I try just a MIN or MAX by itself , like MAX([Months in Invoice Line Item Service Date]) , then I get a slightly different error: " Invalid cast from 'DateTime' to 'Double'. Couldn't store <date> in <bunch of numbers> Column. Expected type is Double. "
Is it truly not possible to calculate this? It seems like there should be at least some way to accomplish this, but I still can't figure it out. (I tried also asking various AI chatbots but none of them were helpful...)
