topic Re: Problem with date in Set analysis in QlikView

Problem with date in Set analysis

Wed, 25 Nov 2020 16:16:04 GMT

Hello

I am new to QlikView and I have a problem with comparing dates in Set analysis.

I have a simple table from SQL CE with columns: [Date1],[Name],[Time] and I want to show in pivot table only names with today's date.

How to do this using Today() function and Set Analysis?

TIA

Re: Problem with date in Set analysis

trdandamudi — Sun, 31 Jul 2016 14:13:10 GMT

Please see the attached and hope this helps. (Today's date is 31-Jul-2016). So I have two records which belong to 31-Jul-2016 and they are displayed on the pivot.

Re: Problem with date in Set analysis

Anil_Babu_Samineni — Sun, 31 Jul 2016 14:24:24 GMT

Can you please Explain more which demonstrates the issue?

Please try to share raw data with expected o/p

Re: Problem with date in Set analysis

sunny_talwar — Sun, 31 Jul 2016 14:55:16 GMT

Read about dates in set analysis here:

Dates in Set Analysis

In general read about dates

Get the Dates Right‌

Why don’t my dates work?

Re: Problem with date in Set analysis

Sun, 31 Jul 2016 18:58:53 GMT

Thanks for answer, but i cannot opern it since I have only Personal edition of Qlikview.

Can you paste the expression?

I tried something like this:

Sum({<[Date1]={"$(=Date(Today()))"}>} Time)

but it doesn't work

Re: Problem with date in Set analysis

sunny_talwar — Mon, 01 Aug 2016 10:05:00 GMT

What is your date field format here? May be you have to do something like this:

Sum({<[Date1]={"$(=Date(Today(), 'DateFieldFormatHere'))"}>} Time)

Where this -> DateFieldFormatHere is a placeholder and replace it with your Date1 Field's format

Re: Problem with date in Set analysis

trdandamudi — Mon, 01 Aug 2016 10:45:34 GMT

Here is the expression:

Sum({<[Date 1]={'$(=Today())'}>} Time)

If this doesn't work, please follow what Sunny is suggesting. Hope this helps...

Re: Problem with date in Set analysis

Mon, 01 Aug 2016 17:50:53 GMT

Thanks, but it doesn't work

Re: Problem with date in Set analysis

Mon, 01 Aug 2016 17:54:10 GMT

I tried to format it but it doesn't help.

BTW I don't really understand why should I format it since it is actually a number (when I put the value of the filed Date1 to table and format it as a number is is shown like 42582 or similiar.

Re: Problem with date in Set analysis

trdandamudi — Mon, 01 Aug 2016 18:26:17 GMT

Please provide a sample app, so that we can take a look and suggest...

Re: Problem with date in Set analysis

sunny_talwar — Mon, 01 Aug 2016 18:27:46 GMT

If it is number, then try this

Sum({<[Date1]={"$(=Num(Today()))"}>} Time)

Re: Problem with date in Set analysis

Mon, 01 Aug 2016 18:37:12 GMT

Here is the sample. nl2.sdf is the datasource. The sample is in Polish, Czas means Time and Data1 is Date1.

Thanks for help

Re: Problem with date in Set analysis

sunny_talwar — Mon, 01 Aug 2016 19:18:11 GMT

Try this:

Sum({<[Data1]={"$(=Date(Today(1), 'YYYY-MM-DD hh:mm:ss'))"}>} Czas)

For your attached sample you don't have Today's date in it, so I am posting the sample qvw with yesterday's date

Sum({<[Data1]={"$(=Date(Today(1)-1, 'YYYY-MM-DD hh:mm:ss'))"}>} Czas)

Re: Problem with date in Set analysis

trdandamudi — Mon, 01 Aug 2016 19:37:10 GMT

As you do not have personal edition I am not attaching the file. As you do not have any records for today's date I did used yesterday date.Please use the below expression:

For yesterday's date:

Sum({<[date(Date#(Data1,'DD-MM-YYYY hh:mm:ss'),'DD/MM/YYY')]={"$(=Date(Today(1)-1"}>} Czas)

Sum({<[Data1]={"$(=Date(Today(1)-1, 'YYYY-MM-DD hh:mm:ss'))"}>} Czas)

For Today's date use the below expression:

Sum({<[date(Date#(Data1,'DD-MM-YYYY hh:mm:ss'),'DD/MM/YYY')]={"$(=Date(Today(1)"}>} Czas)

Sum({<[Data1]={"$(=Date(Today(1), 'YYYY-MM-DD hh:mm:ss'))"}>} Czas)

Also please note that Sunny also provided a working solution. So feel free which ever expression you want to use. Hope this helps.

Re: Problem with date in Set analysis

Mon, 01 Aug 2016 19:45:26 GMT

Thanks both solutions are great!

Re: Problem with date in Set analysis

trdandamudi — Mon, 01 Aug 2016 20:04:18 GMT

Thanks for teaching us a little polish.... Happy Qliking.....