Check if the same date across different records is in the same month or not

Summary: Jake Raz, Jake Raz, is seeking a solution in Sisense v8.2.1.10110 for identifying invoices with line items that have service dates in different months. Jake encountered issues using formulas like MDIFF, MIN, and MAX, which either resulted in errors or incorrect outcomes. Piotr_qbeeq suggests normalizing dates to a Year-Month format to compare them, either through a custom column using a CASE statement or by creating a YYYYMM key. This approach could provide a more reliable comparison, sometimes requiring the use of lookup() for dates from different tables. Piotr from QBeeQ offers this advice, drawing from experience as a Sisense Gold Implementation Partner.

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...)

Jake Raz

Posted 4 months ago · Edited 3 months ago·Last reply 2 months ago

2 comments

Chris Wallingford

·2 months ago · Edited

Hi Jake,

Spitballing...

Could you flag up Invoices that break the rule, returning the unique count of service months within an invoice. This should return the same count on every service date row within the invoice. Then filter the value to only return results > 1.

( [# Unique Months in Line Item Service Date] , ALL( [Days in Line Item Service Dates] ) )

Piotr_qbeeq

·3 months ago

Hi Jake

You can handle this by normalizing both dates to the same Year-Month value and then comparing them.

For example, in a custom column or formula:

CASE 
    WHEN YEAR([Date1]) = YEAR([Date2])
     AND MONTH([Date1]) = MONTH([Date2])
    THEN 'Same Month'
    ELSE 'Different Month'
END

Another clean option is to create a YYYYMM key:

YEAR([Date1]) * 100 + MONTH([Date1])

and compare it with the same expression for [Date2].

This is usually more reliable than comparing formatted date strings.

If the dates are coming from different tables, you may first need to use lookup() to bring them into the same context before applying the comparison.

Hope this makes sense and helps.

Best Regards,
Piotr from QBeeQ, a Sisense Gold Implementation Partner
www.qbeeq.io