Values Wrong When Time Dimension Added to Columns

ryan-staley

New to Birst and I'm not quite understanding why I'm getting the results below.

What I am after and what is correct in Infor - CSD:

Row Labels	1	2	3	4	5	Grand Total
Fake Vendor	16973.71	64404.61	89786.48	64187.75	4217.76	239570.31
Grand Total	16973.71	64404.61	89786.48	64187.75	4217.76	239570.31

 

What I get in visualizer:

Row Labels	April	May	Grand Total
Fake Vendor	46,292.48	2,824.30	49,116.78
Grand Total	46,292.48	2,824.30	49,116.78

The visualizer results are only values from 3 invoices from the filtered date range instead of the full value of all invoices for the filtered date range. The correct total (239570.31) appears when I remove the time dimension from the list of columns. Would appreciate any ideas.

SELECT USING OUTER JOIN [APET.apet_invdt] 'COL0' , [APSV.apsv_name] 'COL1' , [Time.Month] 'COL2' , [Time.Year] 'COL3' , [apet_invdt: Sum: apet_amount] 'COL4' FROM [ALL] WHERE ( ( [APET.apet_Transaction Type]='Invoice' ) AND ( [APET.apet_invdt]>=#2021-01-01# AND [APET.apet_invdt]<=#2021-05-25# ) AND ( [APSV.apsv_name]='Fake Vendor' ) )

Legacy Contributor

Are you confident that apet_invdt is always populated?  I suggest you remove the time dimension from the columns and from the WHERE clause, and then add apet_invdt as a column in the report to see if there are transactions where apet_invdt is null.