topic Re: set analysis - date condition in QlikView

set analysis - date condition

phoenix — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: set analysis - date condition

adamdavi3s — Mon, 20 Feb 2017 14:40:03 GMT

Maybe this?

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

Re: set analysis - date condition

phoenix — Mon, 20 Feb 2017 14:49:53 GMT

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.

Re: set analysis - date condition

phoenix — Mon, 20 Feb 2017 15:21:23 GMT

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.

Re: set analysis - date condition

adamdavi3s — Mon, 20 Feb 2017 15:26:40 GMT

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

Re: set analysis - date condition

Anil_Babu_Samineni — Mon, 20 Feb 2017 15:28:56 GMT

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?

Re: set analysis - date condition

phoenix — Mon, 20 Feb 2017 18:24:09 GMT

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

Re: set analysis - date condition

sasiparupudi1 — Tue, 21 Feb 2017 11:58:18 GMT

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

Re: set analysis - date condition

phoenix — Tue, 21 Feb 2017 15:17:35 GMT

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.