set analysis - date condition - Qlik Community

phoenix · ‎2017-02-20

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

adamdavi3s · ‎2017-02-20

Maybe this?

sum({<CODE_DT_YR_MTH={201609},(date(date#(code_dt_me, 'DDMMMYYYY'),'YYYYMMDD')={"<=$(=date(date#(TranDate_ME, DDMMMYYYY'),'YYYYMMDD'))"}>}Amount*-1) )

phoenix · ‎2017-02-20

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.

phoenix · ‎2017-02-20

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.

adamdavi3s · ‎2017-02-20

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

Anil_Babu_Samineni · ‎2017-02-20

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?

Best Anil, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful

phoenix · ‎2017-02-20

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
];

sasiparupudi1 · ‎2017-02-21

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

phoenix · ‎2017-02-21

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.