topic Re: Calculate Previous Month Record Count in App Development

Calculate Previous Month Record Count

asmith_rc_23 — Mon, 06 Mar 2023 18:34:34 GMT

We have a calculation in an app that Gets count of records from previous month.
All individual variables seem to be working.
however, the calculation returns zero.

Calc= count({$<RecordCurrent_Month = {"Previous_Month_End"}>}Month_end_note_id)

WHERE

RecordCurrent_Month = date(DATA_DATE,'MM/YY')

Previous_Month_End = date(addmonths(DATA_DATE,-1),'MM/YY')

Re: Calculate Previous Month Record Count

Gabbar — Tue, 07 Mar 2023 15:36:35 GMT

This set analysis is going row wise, so there is no data where current_month=previous_month in backend in the same row,
try this
Calc= count({$<RecordCurrent_Month = {"=$(=date(addmonths(DATA_DATE,-1),'MM/YY') )"}>}Month_end_note_id) it should

work when data can be in tabular format like in charts and table.

Re: Calculate Previous Month Record Count

asmith_rc_23 — Tue, 07 Mar 2023 16:14:13 GMT

Gabbar,

Thanks for reaching out.

i had to replace data_date but otherwise i copied the above. I verified that the addmonths calc works too.

count({$<RecordCurrent_Month = {"=$(=date(addmonths(common_loan_wam_month_end_date,-1),'MM/YY') )"}>}Month_end_note_id)

The above returns zero still. I am using a pivot table, not sure if that plays a factor.

Thank you,

Alec

Re: Calculate Previous Month Record Count

Gabbar — Tue, 07 Mar 2023 17:00:05 GMT

Try this:
count({$<RecordCurrent_Month = {"=$(=date(addmonths(RecordCurrent_Month,-1),'MM/YY') )"}>}Month_end_note_id)

Re: Calculate Previous Month Record Count

asmith_rc_23 — Tue, 07 Mar 2023 18:18:02 GMT

No luck. date(addmonths(RecordCurrent_Month,-1),'MM/YY') is valid however.

Results in the screen shot, even included the previous month calculation

Re: Calculate Previous Month Record Count

Gabbar — Wed, 08 Mar 2023 08:31:03 GMT

Please share a sample dataset for the same.

Re: Calculate Previous Month Record Count

asmith_rc_23 — Wed, 08 Mar 2023 15:20:04 GMT

Sure, here is an xlsx of the non-sensitive data involved.

This should be enough @Gabbar

Re: Calculate Previous Month Record Count

Gabbar — Thu, 09 Mar 2023 05:47:39 GMT

Will update the solution as soon as i am able to find, till then
try this:
Above('Your Normal Set expression of current month')
Sort your data in ascending order according to recordcurrent_month

Re: Calculate Previous Month Record Count

rpennacchi — Mon, 13 Mar 2023 11:58:13 GMT

I am sharing a .qvf file showing how I would solve this using The As Of Table.

All you need to know about that is described on this post: The As Of Table . I don't think I can explain better than Hic.

I hope it solve your needs.

Re: Calculate Previous Month Record Count

asmith_rc_23 — Fri, 17 Mar 2023 17:10:22 GMT

So sorry for the delay. @rpennacchi solution worked.

Thank you all for your efforts.