matrix generates unwanted additional row

[GerDec]

Hi, 

We use a matrix for matching production with invoicing. The matrix shows per customer and customer product (max 5) if an invoice has been sent in every month. 

Dimensions: several datapoints per customer. It includes the customer sign on date. In the attachment it is April 19th.

Columns: months.

Measure is the latest invoice number : =FirstSortedValue (invoices.invoiceNumber, -invoices.bookdate)

Issue: whenever the start date of the customer sign on date is  equal to the month in the column an additional row is generated.

Tried to solve it by:

- excluding the sign on dimension,

- calculating the measure based on customer id (thus excluding the sign on date dimension)

- using aggr for specific dimensions

- monthstart max

What would be a way to avoid generating an additional row?

thank you

Ger

[vinieme12]

use a calculated dimension for the date column

AND UNCHECK  Include Null Values for this calculated dimension

[GerDec]

Thank you for your reply.

Your suggestion not the solution... I narrowed down the problem.

Basically the problem is that we have a matrix with approximately  10 dimensions.  It includes the customer information dimensions and several date dimensions (sign on date - contract date - approval date) which are added for information purpuses only (to get a full overview of the customer) They need not to be included in de calculations of the matrix. 

Tried to exclude the date dimensions with AGGR but without any impact.

would there be another solution?

Regards 

[vinieme12]

can you post a sample app?

[GerDec]

Hi Veneeth, thank you again for your reply.

I have uploaded an excel file to explain better. 

First columns are the dimensions, customer, product, product code, start date of deliveries, end date deliveries, start date contract periode, end date contract periode. 

For each product there can only be one row. 

The matrix generates the lateste invoice number for each row (if there have been deliveries).

The multiple dates are causing the creation of an unwanted additional row... it only happens when the start date of the deliveries is not the first of the month.

Tried to circumvent the date dimension with AGGR but without the desired effect

aggr(max (invoices.invoiceNumber), clients.Name, products.code, invoice_rows.beginDate)

Hope the explaination helps.. looking forward to hear from you

Regards