Displaying only the recent value.

[#1]

Hi Everyone,

I have columns store_region and store_end_date and year column as enclosed in the spreadsheet.I need to show only the store region which has store_end_date as null.

EX:In the enclosed spreadsheet for year 2009 the store_region sholud be displayed as 20 instead of 10 and 20 as 20 is pertaining to store_end_date as null.

Please suggest if theres a way to get this working.

Thanks,

Swetha

[#2]

Hello,

Maybe you can try with this formula:

=RIGHT(Min(MONTHEND(STORE_END_DATE) & STORE_REGION), 2)

I haven't tried it, but it should work.

Steve

[#3]

Or use the statement

IsNull(store_end_date)

to select the information you want.

It gives a boolean value back.

[#4]

Thanks everyone for the replies.I could get to the most of it except am stuck at one point.

Please see the enclosed doc.in Year 2009 there are 2 store_region where in I have select only one.I cannot generalise my statement as saying isnull(store_end_date) as in other years where there is no store_end_date as null then also I need to select the store_region.

Hers is the expression am using for store_region:

=if(status='Current' and isnull(STORE_END_DATE)=-1 and aggr(count(distinct STORE_REGION),calendar_year)>1,STORE_REGION,if(status='Current' and isnull(STORE_REGION)=-1 and aggr(count(distinct STORE_REGION),calendar_year)=1,STORE_REGION,if(status='Current' and isnull(STORE_REGION)<>-1,STORE_REGION,' ')))

My requirement is as below:

I have to select store_region only is status is current incase there is not store_end_date as null

in case of store_end_date as null and status is current i have to select only that store_region.

Pls suggest.

Appreciate all your help.

Thanks.

[#5]

HI everyone,

When I am writing the expression:

if(aggr(count(distinct STORE_REGION),calendar_year)>=1 ,STORE_REGION,'-') it gives me the correct result but am having Store_region,store_district and store_number as a cyclic group,how do I incorporate the above expression in this cyclic group.As I do not get the result when I use the same expression in cyclic group.

Pls advice.

[#6]

Hello,

Do you mean how to get the current field of your cyclic group? If so, you can use: GetFieldSelections(CyclicGroupName). It'd something like this:

if(aggr(count(distinct $(=GetFieldSelections(CyclicGroupName))),calendar_year)>=1,

$(=GetFieldSelections(CyclicGroupName)), '-')

Steve

[#7]

Thanks a lot Steve.I got it.

I have one last question.Please refer to the enclosed doc.I have rounded 2008 year,I should get as per the formula-

if(aggr(max(store_key),calendar_year),store_region,' ') 11 as output as 48911 store_key corresponds to store_region 11.But its giving me 8.Is there any limitation in qlikview .Pls help in understanding where am going wrong.

Thanks,

Swetha