Rolling 14 days data

[Darrenkeirl]

Can someone help me please.

i am trying to generate a graph in qlilview that shows the last 14 days with of data on a daily basis (ie there will be 1 bar for each day for the last 14 days)

please could someone help me the the correct set analysis expression to write

[vamsee]

My bad did the column name change the other way round. It should've shown an error though!

Anyway try

=Sum({$<[Full Name]={'Darren Keirl','Chris Phillips','Gemma Young','Micheal
Heaney','Ginnie Brunning','Terry Crampton','Lisa Lewer','Emma
Hares','Julita Wakefield','Liz Halliwell','Sandra Searle'},
CalendarDay ={">=$(=Date(Max(CalendarDay )-14))<=$(=Date(Max(CalendarDay)))"} >}
[TotalExternalInboundAnswered]+[TotalExternalInboundAnswered])

[Darrenkeirl]

Vamsee

Thanks again for your reply however the new expression is now pulling no
data at all.

Would the dimension I have selected make any difference?

[vamsee]

Yes, selected dimension values make a difference To check that create a table box and see if you have rows in it.

If you do, then check the formatting for CalendarDay.

• Create a Text Box and Check

=Date(Max(CalendarDay)-14)

[Darrenkeirl]

Sorry Vamsee but I do not know how to do that.

Currently I have 2 dimensions selected, they are: CalenderDay & Full Name.

How do I check the formatting for CalenderDay?

I promise I will learn lol

[vamsee]

haha.. you don't have to promise me though but gradually, you'll understand Qliking is fun! 

I would've suggested sharing your qvw but as you said you wanted to learn.

Create a table box

Create a Text Box

In the table box, add the expression provided above and see if you get a date value. Then select some other date and see if populates.

[Darrenkeirl]

I will have a play with this so thanks for that and I will keep you updated
on my progress

[Darrenkeirl]

Hi Vamsee

I have set up the table box and I noticed that the format for CalendarDay is 'dd' which is perhaps have the issue?

I have CalendarDay selected as a dimension as well as written into the expression.

Can you suggestion a way I can resolve this?

[vamsee]

DD should work. How about the text box? Did you try that?

[Darrenkeirl]

Vamsee

I just did that as well using the expression you provided earlier:

=Date(Max(CalendarDay)-14)

and i got the following result:

'16/01/1900'

I also change the data selection a couple of time and it did alter my result however not displaying the correct data at all

[vamsee]

Okay now, here is the thing, the sample file you provided only has CalendarDay, which is Day.

In the original source do you have a column which is an actual date like 6/30/2020.

If so, use that instead of CalendarDay, else if your data is limited to one month only then probably try

=Date(Date#(Max(CalendarDay), 'DD'), 'DD')

 If this works your final expression could be 

=Sum({$<[Full Name]={'Darren Keirl','Chris Phillips','Gemma Young','Micheal
Heaney','Ginnie Brunning','Terry Crampton','Lisa Lewer','Emma
Hares','Julita Wakefield','Liz Halliwell','Sandra Searle'},
CalendarDay ={">=$(=Date(Date#(Max(CalendarDay), 'DD')-14), 'DD')<=$(=Date( Date#(Max(CalendarDay), 'DD'),'DD'))"} >}
[TotalExternalInboundAnswered]+[TotalExternalInboundAnswered])

 or  you can also try

=Sum({$<[Full Name]={'Darren Keirl','Chris Phillips','Gemma Young','Micheal
Heaney','Ginnie Brunning','Terry Crampton','Lisa Lewer','Emma
Hares','Julita Wakefield','Liz Halliwell','Sandra Searle'},
CalendarDay ={">=$(=Max(CalendarDay)-14)<=$(=Max(CalendarDay))"} >}
[TotalExternalInboundAnswered]+[TotalExternalInboundAnswered])