To generate report of payment received from one da...

Shashwat_Hemato · 02-28-2020 07:16 PM

I am working on a software for clinic. I have a Table which takes entry of my patients “date” wise. I have another column “Payment Received”.
I want to generate report of sum of payment received from “one particular date” to " another particular date". How to do it. Please help.

Marc_Dillon

You could try something like this:
SUM(SELECT(payment received[amount] , AND( [date] < “xx/yy/zzzz” , [date] > “xx/yy/zzzz”)))

Shashwat_Hemato

What I am doing wrong?

Marc_Dillon

I see you’re setting a row filter condition. Not sure about your strategy here, but if you’re trying to create a slice with only the records in certain dates, then forget about the SUM(SELECT()) parts, just use the logical AND().

It is _THISROW, not _THISRAW.
Also, _thisrow is not an expression in itself, it is part of a deref, as in [_THISROW].[column].
And it should not be in quotation marks.
And you can’t really use it in a slice’s row filter condition.

Are you trying to compare the date to other date columns, or to a specific hard-coded date?

Here’s an example for filtering rows in a slice by date.
AND([date]< "02/29/2020” , [date]>"02/21/2020”)

Maybe you have a 2nd table, called Reports. In that table you have two columns, Date Start, Date End. If you want to sum the payments between those dates, create a virtual column with this expression.

SUM(SELECT(first table[payments received] , AND([date]< [_THISROW].[Date End] , [date]>[_THISROW].[Date Start])))

Shashwat_Hemato

Got it… Thanks for your valuable help

Shashwat_Hemato

I have single table named “All Patients”
Inside that I have one column names [Date] which contains date wise entries of all new walk-in patient.

I have second column name [Payment Received] which contains amounts collected from particular patient…

Now I want to see sum of particular date range in dynamic mode in details view… E.g from 1 February to 15th February…

I am stuck here. I don’t get idea how to do

Shashwat_Hemato

SUM of payment received

Steve

How does the app know what the start and end date are for the range?

Shashwat_Hemato

That is where I stuck… How to make it possible… In my previous software I was able to see date range wise payments

Phil

Are you saying that you want users to be able to enter a starting and ending date for the report?
You then want to display the records between those two dates?

Are you trying to create a workflow report?
If not, can you explain how you want the report to be created?

Shashwat_Hemato

Yes… For example I want to display total collection from 01/02/2020 to 01/31/2020.

Also my accountant want to download data between the date range to maintain 3C form. Or automatically on first of every month, workflow send email (xls) file of last month’s records.

Shashwat_Hemato

Here I have only one column of [Date]

Marc_Dillon

You can make a workflow->report to send monthly. In it you can SUM up all payments from the previous month with the following expression:

SUM(
     SELECT(  table[payments received] , 
                  AND([date] <=  EOMONTH( TODAY() , -1)  ,
                      [date] >=  EOMONTH( TODAY() , -2) + 1   
                     )
            )
    )

Shashwat_Hemato

Thanks… Will try and update you.

Shashwat_Hemato

Shashwat_Hemato

this error occured

Marc_Dillon

That field is the wrong spot to enter that formula. That field is for determining whether to fire the workflow. Please read up on workflows with the above articles. You’ll possibly want to enter that formula in an Email Body Template, and wrap the whole thing in << and >>, as shown in this screenshot.

Shashwat_Hemato

Got it thoroughly… Thanks

To generate report of payment received from one date to another date