Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Announcement: Certain actions are currently causing a Page Not Found error. This is a known issue and we are working with the platform vendor to investigate and resolve it.
pandreozzi
Contributor

Searching Entire Date Field For Meet Condition

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.

13 Replies
Partner
Partner

Re: Searching Entire Date Field For Meet Condition

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 

Highlighted
atkinsow
Valued Contributor II

Re: Searching Entire Date Field For Meet Condition

how about

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

effinty2112
Honored Contributor

Re: Searching Entire Date Field For Meet Condition

Hi Paul,

               Try something like

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

cheers

Andrew

pandreozzi
Contributor

Re: Searching Entire Date Field For Meet Condition

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
Contributor

Re: Searching Entire Date Field For Meet Condition

Andrew what does ItemID represent in your suggestion?

pandreozzi
Contributor

Re: Searching Entire Date Field For Meet Condition

Wallo what does Item represent in your suggestion?

atkinsow
Valued Contributor II

Re: Searching Entire Date Field For Meet Condition

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

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

effinty2112
Honored Contributor

Re: Searching Entire Date Field For Meet Condition

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
Contributor

Re: Searching Entire Date Field For Meet Condition

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