First month sales, after first non zero transaction :)

[M_Zaki]

if i have data like this:

Tx_DATE	Tx_Amount
2018-09-24 3:41	0
2018-09-26 3:42	0
2018-09-28 3:42	0
2018-09-29 3:43	0
2018-09-29 3:44	0
2018-09-29 3:44	-36.2
2018-10-03 4:44	0
2018-10-03 4:44	-36.2
2018-10-10 12:54	400
2018-10-12 13:56	0
2018-10-25 1:53	0
2018-10-29 15:29	-25
2018-11-01 18:43	-42.85
2018-11-02 18:24	-128.57
2018-11-04 2:57	-107.14
2018-11-10 13:53	200

I need to get calculation for sum of "Tx_Amount" between first transaction appeared with value not = zero, and one month later.

I marked the values which i mean with green to clarify, first transaction with "Tx_Amount" not = zero appeared on 2018-09-29 so i need to sum values between 2018-09-29 and 2018-10-29.

[OmarBenSalem]

In yoiur script, in the table containing the date and amount fields, create a flag as follow:

if(Tx_Amount=0,0,1) as FlagAmount

then create a table,

dimension: date

measure:

sum({<Tx_DATE={">=$(=timestamp(min({<FlagAmount={1}>}Tx_DATE),'YYYY-MM-DD hh:mm'))<=$(=timestamp(addmonths(min({<FlagAmount={1}>}Tx_DATE),1),'YYYY-MM-DD hh:mm'))"}>}Tx_Amount)

or

sum({<Tx_DATE={">=$(=timestamp(min({<FlagAmount={1}>}Tx_DATE),'YYYY-MM-DD hh:mm'))<=$(=timestamp((min({<FlagAmount={1}>}Tx_DATE)+31),'YYYY-MM-DD hh:mm'))"}>}Tx_Amount)

Result:

[M_Zaki]

thanks omar

but can we do this without editing script of loading data, the data is huge, as it is representing one year of transactions.

[OmarBenSalem]

That's the biggest reason why u should always edit the script and add some flags or anything to use in the front end when u have huge amount of data.

The script's solution is always the better one dealing with big loads of data.

[OmarBenSalem]

If u don't want to alter ur script, u can do as follow:

sum({<Tx_DATE={">=$(=timestamp(min({<Tx_Amount={"=fabs(Tx_Amount)>0"}>}Tx_DATE),'YYYY-MM-DD hh:mm'))<=$(=timestamp((min({<Tx_Amount={"=fabs(Tx_Amount)>0"}>}Tx_DATE)+31),'YYYY-MM-DD hh:mm'))"}>}Tx_Amount)

  

[M_Zaki]

Dear Omar,

i found a solution as below:

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

but it's not working 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 you help in this, please

[OmarBenSalem]

What if u do as follow:

AGGR(

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

,Account)

[M_Zaki]

No, Not working