topic Re: Set Analysis with Today() expression in QlikView

Set Analysis with Today() expression

Wed, 19 Oct 2016 12:18:35 GMT

Hi guys,

i'm looking for some support.. I'm using this field:

- [Data Stat]= Date (DD/MM/YYYY)

- [Minuti]= it's a number used for the final sum

I'm implementing the set analysis in order to sum all the [Minuti] without using a filter pane in the visualization sheet.

The formula is:

Sum($<[Data Stat]={Today()}<}[Minuti])

But the today function doesn't work and it continues to give me error, can you help me?

Jack

Re: Set Analysis with Today() expression

sunny_talwar — Wed, 19 Oct 2016 16:54:59 GMT

Try like this:

Sum($<[Data Stat] = {"$(=Date(Today(), 'DD/MM/YYYY'))}>} [Minuti])

Re: Set Analysis with Today() expression

Wed, 19 Oct 2016 17:12:02 GMT

Hi Sunny,

thanks for your reply.

It's still doesn't working.. I've used the brackets (") but that counts like an error (the date() and the today() formula aren't shown in blue)

I don't think the problem is in the date format

Thanks

Re: Set Analysis with Today() expression

sunny_talwar — Wed, 19 Oct 2016 17:19:05 GMT

My bad, I missed a double quote at the end:

Sum($<[Data Stat] = {"$(=Date(Today(), 'DD/MM/YYYY'))"}>} [Minuti])

Re: Set Analysis with Today() expression

sunny_talwar — Wed, 19 Oct 2016 17:19:23 GMT

Read here about Dates in Set Analysis

Re: Set Analysis with Today() expression

stabben23 — Wed, 19 Oct 2016 17:44:23 GMT

and the start {

Sum({$<[Data Stat] = {"$(=Date(Today(), 'DD/MM/YYYY'))"}>} [Minuti])

Re: Set Analysis with Today() expression

stabben23 — Wed, 19 Oct 2016 18:03:20 GMT

if you are using qv12, try also today(1)

Re: Set Analysis with Today() expression

toddbuss — Wed, 23 Nov 2016 19:54:17 GMT

Using Staffan's help as a starting point, I'm trying to do a similar set analysis with a date range, but keep coming up with nothing. I suspect my problem is with the cumbersome syntax. My simplified code looks like this:

count({$<ReportDateEnd = {"$(<Date(Today(), 'DD/MM/YYYY'))"}>} TransferReqID)

the underlined "<" is intended to find all dates that are in the past. I'm also looking for a count of cases rather than a sum of values.

Please let me know if I should start a new post rather than tacking my request on to this thread. This is my first post here. I'll continue with reading about Dates in Set Analysis to find clues.

Thanks.

Re: Set Analysis with Today() expression

sunny_talwar — Wed, 23 Nov 2016 19:57:36 GMT

Try putting < outside of the dollar sign expansion and also add = sign before Date

Count({$<ReportDateEnd = {"<$(=Date(Today(), 'DD/MM/YYYY'))"}>} TransferReqID)

Re: Set Analysis with Today() expression

toddbuss — Wed, 23 Nov 2016 20:04:38 GMT

Thanks for the rapid response, Sunny. I appreciate your effort, but I still get zero results with the snippet you provided.

Re: Set Analysis with Today() expression

sunny_talwar — Wed, 23 Nov 2016 20:43:50 GMT

What is the format of ReportDateEnd? Is it DD/MM/YYYY? Also look here for help with dates in set analysis

Dates in Set Analysis

Re: Set Analysis with Today() expression

toddbuss — Mon, 28 Nov 2016 15:41:27 GMT

Thanks. I had to format my dates in the Data Load Editor. The formula you provided works.