topic Re: Comparing two dimension to determine chart data in QlikView

Comparing two dimension to determine chart data

zagzebski — Tue, 01 Oct 2013 18:56:56 GMT

I have the following data:

Group ID	Group Name	Acct Orig Eff Date	Members	Renewal Mth	Premium1	Premium2	Premium3
AAA	AAA Name	20130701	7	7	3,708	3,708	0
BBB	BBB Name	20130701	2	7	520	520	0
CCC	CCC Name	20120101	2	7	1,067	1,095	28
CCC	CCC Name	20120101	2	7	1,096	1,124	29
CCC	CCC Name	20120101	3	7	1,685	1,729	44
CCC	CCC Name	20120101	4	7	2,921	2,998	77
CCC	CCC Name	20120101	2	7	452	464	12

I need to filter on groups where the month in the Acct Orig Eff Date field equals the renewal month AND the year is the max year. So in the case above group id AAA and BBB each have the month of "07" (7 in the case of renewal month) AND the the year is 2013 - the max of the years. So AAA and BBB would NOT be included in the chart.

What is the most efficient way to filter on the above?

Re: Comparing two dimension to determine chart data

manas_bn — Tue, 01 Oct 2013 20:44:25 GMT

Hello,

If you have access to the script on the backend, you can use a preceding load to create another field. It will contain GroupID itself but only for those records which do not have Year = max(Year) and Month = Renewal Month.

Check the code and the result below:

RawData:

Load *,

//If year is max and month is same as renewal month, field will be null, else it will be GroupID

if ( left(AcctOrigEffDate,4)=Year(today()) and mid(AcctOrigEffDate,5,2)=RenewalMth,

null(), GroupID) as RelevantGroupID

Inline [

GroupID, GroupName, AcctOrigEffDate, Members, RenewalMth, Premium1, Premium2, Premium3

AAA, AAA Name, 20130701, 7, 7, 3708, 3708, 0

BBB, BBB Name, 20130701, 2, 7, 520, 520, 0

CCC, CCC Name, 20120101, 2, 7, 1067, 1095, 28

CCC, CCC Name, 20120101, 2, 7, 1096, 1124, 29

CCC, CCC Name, 20120101, 3, 7, 1685, 1729, 44

CCC, CCC Name, 20120101, 4, 7, 2921, 2998, 77

CCC, CCC Name, 20120101, 2, 7, 452, 464, 0

];

Result:

Cheers!

Re: Comparing two dimension to determine chart data

manas_bn — Tue, 01 Oct 2013 20:49:14 GMT

If you intend to NOT make any changes to the script, you can use the below as a calculated dimension and suppress null values.

=if(left(AcctOrigEffDate,4)=Year(today()) and mid(AcctOrigEffDate,5,2),null(),GroupID)

In both the cases, I have used Year(today()) as MAX(YEAR). You can create a variable to get the max(Year)...

Re: Comparing two dimension to determine chart data

zagzebski — Wed, 02 Oct 2013 12:01:52 GMT

Thanks a bunch. This works great!