Rolling 365 days and count of todays activity

[#1]

Hi,

I'm trying to create a line chart to show the following:

* one line to show the activity of today per hour

* one line to show the rolling 365 days averyage per hour

y axis = count of attendance

x axis = hour (so 24 points)

I've created this expression, but it doesnt work, shows no display data.(In green are the different ways I've tried to get it to work)

count({<[ArriveDate]= {'$(vCurrentDay)'}>}[ED_Att_No])


//=count([ED_Att_No])  >>>>>this works and shows data

//=count({<ArriveDate={'20151120'}>} ED_Att_No)
//=count({<[ArriveDate]= {'20/11/15'}>}[ED_Att_No])
//=count({<[ArriveDate]= {'$(vCurrentDay)'}>}[ED_Att_No])
//=count({<[ArriveDate]= {'16/11/2013'}>}[ED_Att_No])

Am i missing something just to do a simple count?!

How would I calculate the rolling average for past 365 days?

Thanks.

[#2]

First, you need to create a Hour field in your Master Calendar.

Create chart Hour as Your Dimension and use below expressions.

Sum({DateField={">=$(=Max(DateField)-365)<=$(=Max(DateField))"}}quantity) --> Last 365 days from the date your selected or default Max date in the app.

Sum({DateField={"$(=Max(DateField))"}}quantity)  -> current selected date or Max date in the app

[ramoncova06]

what is the format of you date field ?

and what expression are you using for vCurrentDay ?

to get the rolling 365 you can use

avg({ArriveDate={">=$(=addyears(Max(ArriveDate),-1))<=$(=Max(ArriveDate))"}}ED_Att_No)

[#4]

Hi Both,

Both suggestions look like they should work, but I'm still not able to get it working.

Have attached qvw to show what I'm going.

[#5]

Thank you. Have added reply to main convo

[#6]

Thank you. Have added reply to main convo