topic date dimension showing only today in QlikView

date dimension showing only today

chriscools — Tue, 12 Jul 2011 21:30:20 GMT

Hello,

i have a pivot table where date is the dimension and several calculated fields behind it.

i want to show this pivot table completely on one page, but on another page, i want to show the same pivot table, but allways only the values of today.

i think this means that i have to include a "calculated dimension" instead of my normal "date" dimension.

i tried with :

date = date() or date = today

but this doesn't work.

is my syntax wrong?

or is my whole approach wrong?

Can anyone help me with this?

thanx!

chris

Re: date dimension showing only today

Tue, 12 Jul 2011 22:06:42 GMT

Hi,

As You wrote You have to make Your calculated dimension.

In my very simple example I've done it like this: =aggr(if(FieldA = today(), FieldA), ID)

I have only 2 columns ID and FieldA with the Date.

The solution can also depend on Your date format and so on.

Regards

wojciechg

date dimension showing only today

chriscools — Tue, 12 Jul 2011 22:17:42 GMT

Hello,

thank you for your answer!

but i can't make it work:

i have this :

=Aggr ( if ( [VOORZIENE_LEVERINGSDATUM] = Today() , [VOORZIENE_LEVERINGSDATUM]))

and it says that there is an error in the expression.

i can't open your example, because i'm working with the free test version... 😞

Re: date dimension showing only today

johnw — Tue, 12 Jul 2011 22:25:50 GMT

This should work:

=aggr(if([VOORZIENE_LEVERINGSDATUM]=today(),[VOORZIENE_LEVERINGSDATUM]),[VOORZIENE_LEVERINGSDATUM])

But if you don't need an aggregation function, I don't think you need the aggr(). So maybe this if I'm right:

=if([VOORZIENE_LEVERINGSDATUM]=today(),[VOORZIENE_LEVERINGSDATUM])

date dimension showing only today

Tue, 12 Jul 2011 22:32:44 GMT

Hi,

Basically after this if function, after comma You have to point on which diemension You're agregating.

In my example from file I had data:

FieldA: ID:

2011-07-01 1

2011-07-02 2

2011-07-03 3

...

And I done the agregation like in my @ above:

In You example the dimension is missing. Unfortunetaly I do not have You file, but please try this:

=Aggr ( if ( [VOORZIENE_LEVERINGSDATUM] = Today() , [VOORZIENE_LEVERINGSDATUM]), [VOORZIENE_LEVERINGSDATUM])

hope that helps!

regards,

wojciechg

date dimension showing only today

chriscools — Tue, 12 Jul 2011 22:51:58 GMT

Hello,

thank you all very much!!!

for some reason the aggr function does not work, i allways get an error then.

but the "=if([VOORZIENE_LEVERINGSDATUM]=today(),[VOORZIENE_LEVERINGSDATUM])"

works fine.

how should i proceed if i want to show not only today but every day between today and the next 10 days?

grtz

Re: date dimension showing only today

johnw — Tue, 12 Jul 2011 23:28:57 GMT

Dates are internally represented as the number of days since December 30 1899, so you add days by just adding an integer. So then you can check a range something like this:

if( [VOORZIENE_LEVERINGSDATUM]>=today()
AND [VOORZIENE_LEVERINGSDATUM]<=today()+10
,[VOORZIENE_LEVERINGSDATUM])

date dimension showing only today

chriscools — Tue, 12 Jul 2011 23:36:34 GMT

thank you very much!

grtz

Re: date dimension showing only today

johnw — Tue, 12 Jul 2011 23:49:10 GMT

I should probably mention that if you have a lot of data, I would expect this calculated dimension approach to perform poorly. A better choice in that case might be to limit the values using set analysis and just use VOORZIENE_LEVERINGSDATUM as the dimension. Set analysis is applied to expressions instead of dimensions. So let's say your pivot table has these expressions:

sum(Revenue)
sum(Cost)

Set analysis is applied separately to each, so you'd need to use the same set analysis expression in each. I think this:

sum({<[VOORZIENE_LEVERINGSDATUM]*={">=$(=today())<=$(=date(today()+10))"}>} Revenue)
sum({<[VOORZIENE_LEVERINGSDATUM]*={">=$(=today())<=$(=date(today()+10))"}>} Cost)

While you're not specifically restricting the dimension this way, the set analysis makes certain that you calculate nulls for dates that you aren't interested in. They are then suppressed automatically.

date dimension showing only today

chriscools — Tue, 12 Jul 2011 23:58:19 GMT

Hey John,

thanx for the extra explanation!

set analysis should work faster then?

and then i get to use the [voorziene_leveringsdatum] as a normal dimension then?

i should look up the semantic for this set analysis, because with all the symbols its not very clear to me.

and with this set analysis i can also take multiple restrictions into consideration?

for example the dates between today and + 10 and exclude for example values "saterday" and "sunday" in a "dayname"-field?

grtz

Re: date dimension showing only today

johnw — Wed, 13 Jul 2011 00:07:08 GMT

Yes, you would then get to use VOORZIENE_LEVERINGSDATUM as a normal dimension.

If you have a large data set, I would expect set analysis to work faster. I MAY be wrong, and it is always worth testing if you see performance problems. Basically, performance differs from version to version, and differs depending on your data model and chart, so it's difficult to say for certain that one approach is better than another for any specific case. All that said, set analysis seems to win the majority of speed contests. It's also very flexible.

Unfortunately, as you've noticed, the syntax for set analysis is pretty dense. But yes, you can take multiple restrictions into consideration. So for example:

sum({<[VOORZIENE_LEVERINGSDATUM]*={">=$(=today())<=$(=date(today()+10))"}
,dayname-={'saterday','sunday'}>} Revenue)

Re: date dimension showing only today

johnw — Wed, 13 Jul 2011 00:19:39 GMT

Hmmm, the FASTEST approach in a case like this would probably be to identify the days of interest during the load itself, specifically the load of a calendar table.

,if([VOORZIENE_LEVERINGSDATUM]>=today()
AND [VOORZIENE_LEVERINGSDATUM]<=today()+10
AND NOT match(dayname,'saterday','sunday')

,[VOORZIENE_LEVERINGSDATUM]) as "Only Display These Dates"

Then use "Only Display These Dates" as the dimension in your chart (obviously change the name to something that makes more sense). That's what I would probably personally do. That might also be more clear than set analysis. I tend to prefer script solutions like this to set analysis solutions as long as I am not doing any accumulations in the script.

In order of decreasing performance in my experience:

Script
Set analysis
Conditional aggregation like sum(if(...))
Calculated dimensions