Searching Entire Date Field For Meet Condition

[pandreozzi]

I have a challenge. I have a date field in my table called Billing_End which represents when an item stops billing. This Billing_End date field is used on multiple sheets. My challenge is hide or show a sheet called Billing End Date Search based on this field. This called for searching the entire table for any date the is greater than the date today. I did get part of this to work with the following code.

=if(BILLING_END <= vDate,1,0)

What happens with this code is as soon as the first condition is meet the code exits and thinks that is ok. My problem is with multiple items tied to one customer have more than one Billing_End date the Billing End Date Search sheet needs to be displayed for example.

A customer may have the following:

item A billing start date 01/01/2015 billing end date 01/01/2099
item B billing start date 01/01/2016 billing end date 06/30/2017

With this scenario and my code the condition is meet based on the billing end date of item A so the Billing End Date Search sheet hides and the next item "B" is never considered. If item "B" is considered than the Billing End Date Search sheet will be visible.

Thanks for any suggestions.

[pandreozzi]

This also worked

=if(min(Aggr(only(BILLING_END), ATBILLINGSITEID)) <= vDate,1,0)

[pandreozzi]

Sorry to say the third option gave me an expression error.

[pandreozzi]

Can someone break down this expression for me in simple terms.

=if(min(Aggr(only(BILLING_END), ATBILLINGSITEID)) <= vDate,1,0)

I get the if statement but what does the min, Aggr and only do as this expression is being executed?

My take is that the min is looking at the variable defined and the aggr forces the expression to look at the entire table contends of the ATBILLINGSITEID and BILLIN_END. I am not sure what only does. If I am wrong please correct me.

[lironbaram]

here is my try

the min function find the minimal value of the aggr function

the aggr function builds a virtual table with ATBILLINGSITEID  as the dimension and

only (BILLING_END) as the expression , the expression will return a value only for the

ATBILLINGSITEID   values who as one BILLING_END value otherwise it'll return null

so in short the expression find the minimal BILLING_END for the ATBILLINGSITEID  value who has on BILLING_END value

and compare this value to the variable