topic Re: Calculation condition in table box in QlikView

Calculation condition in table box

shaktisinghchau — Wed, 25 Nov 2020 16:16:04 GMT

Greetings,

I have a table box where I want to show employees whose has status date in between start and end date variable, I checked all these 3 field has datatype of dates and format is MM/DD/YYYY.

I tried following expression but did not working, my table box is showing data beyond start/end date.

EmployeeStatusDate >= vStartDate and EmployeeStatusDate <= vEndDate

EmployeeStatusDate >= $(=vStartDate) and EmployeeStatusDate <= $(=vEndDate)

Re: Calculation condition in table box

dinuwanbr — Mon, 18 Sep 2017 08:04:51 GMT

Hi Shakthi,

Can you try following date() or date#()

EmployeeStatusDate >= Date(vStartDate,'MM/DD/YYYY') and EmployeeStatusDate <= Date(vEndDate,'MM/DD/YYYY')

if not working is it possible to upload a sample here?

Rgds,

Tharindu

Re: Calculation condition in table box

shaktisinghchau — Mon, 18 Sep 2017 08:11:09 GMT

It is not working, thanks for quick response!!

Re: Calculation condition in table box

dinuwanbr — Mon, 18 Sep 2017 08:13:12 GMT

Hi Shakthi,

Is it possible to upload a sample?

Rgds,

Tharindu

Re: Calculation condition in table box

prieper — Mon, 18 Sep 2017 08:39:21 GMT

Looks like a formatting problem.

When working with dates, it makes often sense to restrict the comparisons on the pure numbers, rather than trying to workaround with different format-settings.

Re: Calculation condition in table box

shaktisinghchau — Mon, 18 Sep 2017 10:23:06 GMT

I can not upload qvw it is at client end alos size of the file is huge

Re: Calculation condition in table box

shaktisinghchau — Mon, 18 Sep 2017 10:32:02 GMT

Thanks Peter but I have tried via number comparison as well but it did not work...

Re: Calculation condition in table box

Peter_Cammaert — Mon, 18 Sep 2017 10:34:43 GMT

AFAIK a Table Box doesn't offer filtering or expression evaluation capabilities. Except for the object itself...

Re: Calculation condition in table box

prieper — Mon, 18 Sep 2017 10:43:02 GMT

you are correct, have overlooked this condition from the OP

Re: Calculation condition in table box

Peter_Cammaert — Mon, 18 Sep 2017 12:01:43 GMT

Usually, Table Boxes can be converted into Straight Tables. And those offer all expression and conditional show tools that you want. The speediest solution would be a straight table with an expression that uses Set Analysis to reduce the number of rows shown...

Re: Calculation condition in table box

omkarvamsi — Mon, 18 Sep 2017 12:51:55 GMT

Hi Shakthi,

I have attached the application which can filter out dates between start date and enddate.

i did converted the dates to numbers in both variable and also dates from the field tooo.

i used set analysis and filtered the data.

check and confirm.

Regards

Re: Calculation condition in table box

shaktisinghchau — Tue, 26 Sep 2017 11:17:03 GMT

Hello Everyone,

Calculation condition in table box or any other chart control will never filter internal data, this calculation condition accept true or false expression and if it true then only show data over table or chart.

To filter internal data of a field make it a dimension and write If expression on top of it, return null in case of false condition and check the "Suppress When Value is Null" check box like I am doing below.