topic Re: Adding Date to set analysis in QlikView

Adding Date to set analysis

kevbrown — Mon, 18 Jul 2016 13:01:14 GMT

I'm looking at some set analysis and have the following -

=Count( { < [Specialty Code] = { '700' } > } [Bed Days.Occupied])

I want to add a date criteria to is so it only looks at yesterdays date. My date field is called "Date"

Re: Adding Date to set analysis

sunny_talwar — Mon, 18 Jul 2016 13:03:55 GMT

May be like this:

=Count({<[Specialty Code] = { '700' }, Date = {"$(=Date(Today() -1, 'DateFieldFormatHere'))"}>} [Bed Days.Occupied])

Re: Adding Date to set analysis

kevbrown — Mon, 18 Jul 2016 13:14:09 GMT

That works (however I don't need the datefieldformat as I'm just looking for a number in a text box. Problem is now that when i make a date selection the number changes, I want it to be today no matter what the user selects

Re: Adding Date to set analysis

Anonymous — Mon, 18 Jul 2016 13:16:59 GMT

based on sunny's solution and your query: ............I want it to be today no matter what.................

try below ?!!

=Count( { 1 <[Specialty Code] = { '700' }, Date = {"$(=Date(Today() -1, 'DateFieldFormatHere'))"}>} [Bed Days.Occupied])

Re: Adding Date to set analysis

sunny_talwar — Mon, 18 Jul 2016 13:18:03 GMT

I don't think this should happen, can you share a sample where this is happening for you?

Re: Adding Date to set analysis

sunny_talwar — Mon, 18 Jul 2016 13:18:18 GMT

I don't think 1 should bee needed

Re: Adding Date to set analysis

kevbrown — Mon, 18 Jul 2016 13:26:16 GMT

It's the text box, Occupied Bed Days

Re: Adding Date to set analysis

sunny_talwar — Mon, 18 Jul 2016 13:29:51 GMT

Are you making selection in Date or are you making a selection in Day, Month, Year fields??? If Day, Month and Years are the fields you are making selections in, then you probably need this:

=Count({<[Specialty Code] = { '700' }, [Date] = {"$(=Date(Today() -1))"}, InfoCalDay, InfoCalMonth, InfoCalYear>} [Bed Days.Occupied])

Re: Adding Date to set analysis

kevbrown — Mon, 18 Jul 2016 13:31:42 GMT

Perfect, thank you

Re: Adding Date to set analysis

kevbrown — Mon, 18 Jul 2016 13:34:26 GMT

How about the empty beds box?

Re: Adding Date to set analysis

kevbrown — Mon, 18 Jul 2016 13:35:56 GMT

Think I've done it

=Sum( { <[Specialty Code] = { '700' }, [Date] = {"$(=Date(Today() -1))"}, InfoCalDay, InfoCalMonth, InfoCalYear>} [Available Beds.Beds Available])-

Count( { <[Specialty Code] = { '700' }, [Date] = {"$(=Date(Today() -1))"}, InfoCalDay, InfoCalMonth, InfoCalYear>} [Bed Days.Occupied])

Re: Adding Date to set analysis

sunny_talwar — Mon, 18 Jul 2016 13:36:44 GMT

Looks good to me if it gives you what you want

Re: Adding Date to set analysis

kevbrown — Fri, 29 Jul 2016 13:37:14 GMT

Why is this not working?

=Count({<[Specialty Code] = { '700' },[Outcome] = { 'Attended' }, Date = {"$(=Date (Today() -1))"}, InfoCalDay, InfoCalMonth, InfoCalYear>} [refrl_refno])

Re: Adding Date to set analysis

maheshkuttappa — Fri, 29 Jul 2016 13:56:19 GMT

try date format to match the Date field , something like below

Date = {"$(=Date(Today() -1,'MM/DD/YYYY'))"}