topic Re: Searching Entire Date Field For Meet Condition in QlikView

Searching Entire Date Field For Meet Condition

pandreozzi — Thu, 13 Jul 2017 14:39:01 GMT

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.

Re: Searching Entire Date Field For Meet Condition

lironbaram — Thu, 13 Jul 2017 14:43:28 GMT

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

Re: Searching Entire Date Field For Meet Condition

Anonymous — Thu, 13 Jul 2017 14:43:37 GMT

how about

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

Re: Searching Entire Date Field For Meet Condition

effinty2112 — Thu, 13 Jul 2017 14:46:10 GMT

Hi Paul,

Try something like

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

cheers

Andrew

Re: Searching Entire Date Field For Meet Condition

pandreozzi — Thu, 13 Jul 2017 14:57:13 GMT

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

Re: Searching Entire Date Field For Meet Condition

pandreozzi — Thu, 13 Jul 2017 15:00:18 GMT

Andrew what does ItemID represent in your suggestion?

Re: Searching Entire Date Field For Meet Condition

pandreozzi — Thu, 13 Jul 2017 15:00:41 GMT

Wallo what does Item represent in your suggestion?

Re: Searching Entire Date Field For Meet Condition

Anonymous — Thu, 13 Jul 2017 15:04:30 GMT

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

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

Re: Searching Entire Date Field For Meet Condition

effinty2112 — Thu, 13 Jul 2017 15:05:23 GMT

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

Re: Searching Entire Date Field For Meet Condition

pandreozzi — Thu, 13 Jul 2017 15:08:19 GMT

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

Re: Searching Entire Date Field For Meet Condition

pandreozzi — Thu, 13 Jul 2017 15:11:26 GMT

This also worked

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

Re: Searching Entire Date Field For Meet Condition

pandreozzi — Thu, 13 Jul 2017 15:12:48 GMT

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

Re: Searching Entire Date Field For Meet Condition

pandreozzi — Fri, 21 Jul 2017 12:09:48 GMT

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.

Re: Searching Entire Date Field For Meet Condition

lironbaram — Fri, 21 Jul 2017 12:57:47 GMT

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