Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

zagzebski
Contributor

Comparing two dimension to determine chart data

I have the following data:

Group IDGroup NameAcct Orig Eff DateMembersRenewal MthPremium1Premium2Premium3
AAAAAA Name20130701773,7083,7080
BBBBBB Name20130701275205200
CCCCCC Name20120101271,0671,09528
CCCCCC Name20120101271,0961,12429
CCCCCC Name20120101371,6851,72944
CCCCCC Name20120101472,9212,99877
CCCCCC Name201201012745246412

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?

1 Solution

Accepted Solutions
Highlighted
manas_bn
Contributor

Re: Comparing two dimension to determine chart data

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)...

3 Replies
manas_bn
Contributor

Re: Comparing two dimension to determine chart data

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:

Capture.PNG.png

Cheers!

Highlighted
manas_bn
Contributor

Re: Comparing two dimension to determine chart data

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)...

zagzebski
Contributor

Re: Comparing two dimension to determine chart data

Thanks a bunch. This works great!

Community Browser