date dimension showing only today

[chriscools]

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

[#2]

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

[chriscools]

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... 😞

[johnw]

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])

[#5]

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

[chriscools]

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

[johnw · Accepted solution]

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])

[chriscools]

thank you very much!

grtz

[johnw]

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.

[chriscools]

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