calculating sum amount for one month after first non zero transaction

[M_Zaki]

Dears,

below experssion should calculate sum amount for one month after first non zero transaction:

Sum({$<Tx_UPDATE_DATE={">=$(=timestamp(min({<[Tx_Amount]-={'0'}>}Tx_UPDATE_DATE)))<=$(=timestamp(addmonths(min({<[Tx_Amount]-={'0'}>}Tx_UPDATE_DATE),1)))"}>} Tx_Amount)

it's calculating invalid numbers, when I'm not applying filter on account as per this screenshot:

this value 7.57 and all of the above  are wrong values.

but when I'm choosing specific account, it's working right, as per this screenshot:

this value -23.96 is right value.

could anyone help in this, please.

[sunny_talwar · Accepted solution]

I think set analysis isn't going to work for you... try this may be

Sum(Aggr(
   If(Tx_UPDATE_DATE >= Min(TOTAL <Account> {<[Tx_Amount] -= {'0'}>} Tx_UPDATE_DATE) and
      Tx_UPDATE_DATE <= AddMonths(Min(TOTAL <Account> {<[Tx_Amount] -= {'0'}>} Tx_UPDATE_DATE),1), Tx_Amount))
, Account, Tx_UPDATE_DATE))

[M_Zaki]

Sorry, Not working

[M_Zaki]

Working now, only removed additional invalid bracket here:

Sum(Aggr(
   If(Tx_UPDATE_DATE >= Min(TOTAL <Account> {<[Tx_Amount] -= {'0'}>} Tx_UPDATE_DATE) and
      Tx_UPDATE_DATE <= AddMonths(Min(TOTAL <Account> {<[Tx_Amount] -= {'0'}>} Tx_UPDATE_DATE),1), Tx_Amount) )
, Account, Tx_UPDATE_DATE))

Many Thanks 🙂

[M_Zaki]

Dear Sunny,

could you please illustrate why it's not working accurately with Count()?!

Count(
Aggr(
   If(Tx_UPDATE_DATE >= Min(TOTAL <Account> {<[Tx_Type]={'Payment'},[Tx_Amount]-={'0'}>} Tx_UPDATE_DATE) and
      Tx_UPDATE_DATE <= AddMonths(Min(TOTAL <Account> {<[Tx_Type]={'Payment'},[Tx_Amount]-={'0'}>} Tx_UPDATE_DATE),1), Tx_ID)
, Account, Tx_UPDATE_DATE))

or you can say, how can i count specific Tx_Type for previous case?!

[sunny_talwar]

---

@M_Zaki wrote:

could you please illustrate why it's not working accurately with Count()?!

---

Are you looking to do a count or DISTINCT count?

[M_Zaki]

OK,

let's state it like this:

For previous figure, i need to count "payment" transactions (Tx_type field), for every account in this month, which has tx_amount not = 0

[sunny_talwar]

I am not 100% sure, but may be this

Count(DISTINCT 
Aggr(
   If(Tx_UPDATE_DATE >= Min(TOTAL <Account> {<[Tx_Type]={'Payment'},[Tx_Amount]-={'0'}>} Tx_UPDATE_DATE) and
      Tx_UPDATE_DATE <= AddMonths(Min(TOTAL <Account> {<[Tx_Type]={'Payment'},[Tx_Amount]-={'0'}>} Tx_UPDATE_DATE),1), Tx_type)
, Account, Tx_UPDATE_DATE))