chart locked to yesterday, week to date (WTD), Month to date (MTD) and year to date (YTD)

[#1]

Hello all,

I know there are a lot of week to date (WTD), Month to date (MTD) and year to date (YTD) posts but i can not find anything that will fit my problem.

A sheet is loaded daily (see attached excel), from these figures I create the averages per channel using: -

=Num(sum(aggr(sum(PrimaryUptime),Channel)/aggr(count(PrimaryUptime),Channel)),'#,####0.0000')

=Num(sum(aggr(sum(SecondaryUptime),Channel)/aggr(count(SecondaryUptime),Channel)),'#,####0.0000')

which when you select a date or a range of dates does a calculation per channel.

That all works ok but i have been trying to set the charts to a fixed date rather than selecting a date. I have tried using triggers on opening, Duplicate tables, and my last attempt: -

if(num(Day)=num(today()-1),1,0)

then suppress all the zeros.

I have not used set analysis before but gave this a go but did not seem to do anything.

If anyone can recommend any ways to set my charts to Yesterday, WTD, MTD and YTD i would be very grateful.

Cheers

Joe

[#2]

Hello,

look at my little sample application with two OnOpening triggers on the field "Date". Is it that what you intended ?

Regards, Roland

[#3 · Accepted solution]

sorry i worked it out in the end, the trigger that i had set were not working because my dates were a string rather than a date. Thanks for replying so fast.