Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi, I have code_dt_me (month in which code is placed on an account) and trandate_me (transaction month for the accounts) in my data. There is a sale amount corresponding to each trandate_me. I want to calculate the sum(Sale) amount happened in the month of code or after code date month for each code month.
Example data:
For the accounts coded in month of 30sep2016, I want to calcualte the sum(prin_bal_at) for transaction month after code month. For accounts coded in sep'16 month, I want to track the transactions on those accounts from sep'16-jan'17. I should not include any transactions happened before sep'16. in the below data I should not include aug'16 transactions in the SUM.
code_dt_me | TranDate_ME | prin_bal_at |
30SEP2016 | 28FEB2017 | 1124.49 |
30SEP2016 | 28FEB2017 | 11171.36 |
30SEP2016 | 30NOV2016 | 11569.12 |
30SEP2016 | 30NOV2016 | 11718.76 |
30SEP2016 | 30NOV2016 | 12103.61 |
30SEP2016 | 30NOV2016 | 16231.12 |
30SEP2016 | 30SEP2016 | 114.78 |
30SEP2016 | 30SEP2016 | 371.74 |
30SEP2016 | 30SEP2016 | 429.21 |
30SEP2016 | 30SEP2016 | 748.69 |
30SEP2016 | 31AUG2016 | 443.21 |
30SEP2016 | 31AUG2016 | 748.69 |
30SEP2016 | 31AUG2016 | 1550 |
30SEP2016 | 31AUG2016 | 2354.28 |
30SEP2016 | 31AUG2016 | 2501.78 |
30SEP2016 | 31DEC2016 | 8845.53 |
30SEP2016 | 31DEC2016 | 11171.36 |
30SEP2016 | 31JAN2017 | 1770.25 |
30SEP2016 | 31JAN2017 | 1844.76 |
30SEP2016 | 31JAN2017 | 1991.86 |
30SEP2016 | 31JAN2017 | 2354.28 |
30SEP2016 | 31OCT2016 | 1068.83 |
30SEP2016 | 31OCT2016 | 1198.6 |
30SEP2016 | 31OCT2016 | 1199.4 |
I tried this expression but this is not working:
if(date(date#(code_dt_me, 'DDMMMYYYY'),'YYYYMMDD')<=date(date#(TranDate_ME, 'DDMMMYYYY'),'YYYYMMDD'),sum({<CODE_DT_YR_MTH={201609}>}Amount*-1) )
I need help with the expression.
Thanks
Maybe this?
sum({<CODE_DT_YR_MTH={201609},(date(date#(code_dt_me, 'DDMMMYYYY'),'YYYYMMDD')={"<=$(=date(date#(TranDate_ME, DDMMMYYYY'),'YYYYMMDD'))"}>}Amount*-1) )
sum({<CODE_DT_YR_MTH={201609},(date(date#(code_dt_me, 'DDMMMYYYY'),'YYYYMMDD')={"<=$(=date(date#(TranDate_ME, 'DDMMMYYYY'),'YYYYMMDD'))"}>}Amount*-1))
Adam, thanks for the reply. This is giving me all null values.
when I use this in text box I am getting null value=date(date#(code_dt_me, 'DDMMMYYYY'),'YYYYMMDD')
but if I use =max(=date(date#(code_dt_me, 'DDMMMYYYY'),'YYYYMMDD') ) I get 20170131. Not able to understand the logic behind date conversion.
This logic is because in the text box its trying to calculate for multiple dates, when you max it then it can return a result because there is only one value.
Sorry manic afternoon but will try and pick this one up again later
For 1st one you never get any date due to you don't have Aggregation here
And 2nd one should give Max of Date. Here what you are not understand?
test:
load * inline [
Amount, CODE_DT_YR_MTH, code_dt_me, TranDate_ME, Trans_YR_MTH
-412.59, 201609, 30SEP2016, 31AUG2016, 201608
-325.18, 201609, 30SEP2016, 31AUG2016, 201608
-285, 201609, 30SEP2016, 31AUG2016, 201608
-350, 201609, 30SEP2016, 30SEP2016, 201609
-301.37, 201609, 30SEP2016, 30SEP2016, 201609
-83.11, 201609, 30SEP2016, 31OCT2016, 201610
-82.59, 201609, 30SEP2016, 31OCT2016, 201610
-50.13, 201609, 30SEP2016, 31OCT2016, 201610
-1.31, 201609, 30SEP2016, 31OCT2016, 201610
-793.45, 201609, 30SEP2016, 30NOV2016, 201611
-551, 201609, 30SEP2016, 30NOV2016, 201611
-500, 201609, 30SEP2016, 30NOV2016, 201611
-50, 201609, 30SEP2016, 31DEC2016, 201612
-47, 201609, 30SEP2016, 31DEC2016, 201612
-1250, 201609, 30SEP2016, 31JAN2017, 201701
-1000, 201609, 30SEP2016, 31JAN2017, 201701
-941.28, 201609, 30SEP2016, 31JAN2017, 201701
-400, 201609, 30SEP2016, 31JAN2017, 201701
];
Create a flag in the script
test:
LOAD
Date(Date#(CODE_DT_YR_MTH,'YYYYMM'),'YYYYMM') as CODE_DT_YR_MTH,
code_dt_me,
TranDate_ME,
Date(Date#(Trans_YR_MTH,'YYYYMM'),'YYYYMM') as Trans_YR_MTH,
if(Trans_YR_MTH<CODE_DT_YR_MTH,0,1) as flag,
Amount
;
load * inline [
Amount, CODE_DT_YR_MTH, code_dt_me, TranDate_ME, Trans_YR_MTH
-412.59, 201609, 30SEP2016, 31AUG2016, 201608
-325.18, 201609, 30SEP2016, 31AUG2016, 201608
-285, 201609, 30SEP2016, 31AUG2016, 201608
-350, 201609, 30SEP2016, 30SEP2016, 201609
-301.37, 201609, 30SEP2016, 30SEP2016, 201609
-83.11, 201609, 30SEP2016, 31OCT2016, 201610
-82.59, 201609, 30SEP2016, 31OCT2016, 201610
-50.13, 201609, 30SEP2016, 31OCT2016, 201610
-1.31, 201609, 30SEP2016, 31OCT2016, 201610
-793.45, 201609, 30SEP2016, 30NOV2016, 201611
-551, 201609, 30SEP2016, 30NOV2016, 201611
-500, 201609, 30SEP2016, 30NOV2016, 201611
-50, 201609, 30SEP2016, 31DEC2016, 201612
-47, 201609, 30SEP2016, 31DEC2016, 201612
-1250, 201609, 30SEP2016, 31JAN2017, 201701
-1000, 201609, 30SEP2016, 31JAN2017, 201701
-941.28, 201609, 30SEP2016, 31JAN2017, 201701
-400, 201609, 30SEP2016, 31JAN2017, 201701
];
and in the set analysis
sum({<flag={1}>}Amount)
hth
Sasi
Hi Sasidhar, thanks for your reply. that helped. how to approach this if those two date fields are in different tables? I have a star schema data model I am not joining the tables.