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.

[lironbaram]

hi

you need something like this

sum(aggr(if(BILLING_END <= vDate,1,0),Item,Customer))

this function will give you the some of the conditions that are fulfilled per customer and item

so if the result is bigger than 1 you will display the sheet 

[Anonymous]

how about

=count(distinct {BILLING_END={'$<=$(vDate)'}ITEM)>0

[effinty2112]

Hi Paul,

               Try something like

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

cheers

Andrew

[pandreozzi]

These are all great suggestions. I will try each of them as there are typically many answers to a solution. I will repost once I try these.

Thanks

[pandreozzi]

Andrew what does ItemID represent in your suggestion?

[pandreozzi]

Wallo what does Item represent in your suggestion?

[Anonymous]

It's my guess at the name of your field that contains your item names.

i.e., Item A, Item B, etc.

[effinty2112]

Hi,

     ItemID is some identifier for each Item. Since I don't know your data model I don't know what the field name to use should be but try substituting ItemID with the correct field - an item reference or part number or whatever.

Good luck

Andrew

[pandreozzi]

Thanks guys. Below is what seem to have worked. I will continue testing and if something changes I will post. I will also try the other two suggestion.

=sum(aggr(if(BILLING_END <= vDate,1,0),ATBILLINGSITEID,GEMS_ORG_DESCRIPTION))