topic Reporting Date counting records multiple times in App Development

Reporting Date counting records multiple times

SR2 — Thu, 01 Apr 2021 13:46:08 GMT

Hello Experts,

I have created following expression to get a count of invoices -

COUNT(
IF ( ([ Invoice Date]) <= EOMDate,
[Invoice Number]))
)

EOMDate is a reporting variable i.e. end of month for last 12 months to plot a graph.

I see that each invoice is counted as many times as the number of days in the month. Can anyone please suggest why it is counting in this manner. I don't want to use distinct as I do have same invoice number from different supplier in database.

EOMDate created using following script -

Let varMinDate = Num(Makedate(Year(Today())-2,Month(Today()),Day(Today())));
Let varMaxDate = Num(Makedate(Year(today()),Month(Today()),Day(Today())));Datefield:
LOAD MonthEnd(date($(varMinDate)+IterNo()-1)) AS Datefield
AUTOGENERATE (1)
WHILE $(varMinDate)+IterNo()-1<= $(varMaxDate);Calender:
LOAD
Date(Datefield) AS EOMDate,
Year(Datefield) AS RepYear,
Month(Datefield) As RepMonth,
Day(Datefield) AS RepDay,
'Q ' & Num(Ceil(Month(Datefield)/3),'(ROM)0') AS RepQuarter

Thanks a lot!

Re: Reporting Date counting records multiple times

Or — Mon, 22 Mar 2021 20:13:46 GMT

Check that your table keys are correct between the invoice table and the calendar table. When count() fails to return the expected result, that's the first place to look. If your keys appear to be correct, try a simple count([Invoice Number]) while selecting the required date range and see if that gets the correct result - if it does, the issue is likely with the if() statement. If it does not, the issue is probably with the underlying data structure or data.

Re: Reporting Date counting records multiple times

SR2 — Tue, 23 Mar 2021 06:10:09 GMT

Thank you for your reply. I did not join the calendar table with the invoice table. I will join and see if that resolves the issue.

Thanks again!