topic Re: Rolling 14 days data in QlikView

Rolling 14 days data

Darrenkeirl — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Rolling 14 days data

vamsee — Mon, 29 Jun 2020 19:53:13 GMT

Try

Sum({<Date={">=$(=Date(Max(Date)-14))<=$(=Date(Max(Date)))"}>} Sales )

Re: Rolling 14 days data

Vegar — Mon, 29 Jun 2020 22:57:22 GMT

Something like this. Assuming you are trying to calculate Sum(Amount) over the 14 days and that your Date field is DateField.

sum(aggr(rangesum(above(total sum({<MonthField=,YearField=,DateField=>} Amount),0,14)),DateField))

Re: Rolling 14 days data

basav — Tue, 30 Jun 2020 05:45:37 GMT

Hi Darren,

Please mention the field names too. That would help us give you correct answer. I found below expression, this shows 14 days sales in bar chart.

=sum({$<SALESDATE={"=SALESDATE>=Max(total Floor(SALESDATE))-14"}>}SALES)

Refer: https://community.qlik.com/t5/New-to-QlikView/Show-last-15-days-sales-in-a-bar-chart/m-p/585836

cheers,

-Basav

Re: Rolling 14 days data

Darrenkeirl — Tue, 30 Jun 2020 06:53:54 GMT

Morning Basav

Thanks for your reply. Sorry for not including all the information.

The app I am using in qlikview is a KPI App and below is the expression I
am using currently to see the performance data for each week in 2020
(please don't laugh lol).

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"},
CalendarYear= {"2020"}>}[TotalExternalInboundAnswered])
+
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"},
CalendarYear= {"2020"}>}[TotalExternalInboundAnswered])

I have been attempting to edit this expression so I see daily data for the
last 14days.

Re: Rolling 14 days data

basav — Tue, 30 Jun 2020 07:14:16 GMT

Hi Darren, how are you?

What is the dimension in the bar chart you have given?

Can you put the full Name in the dimension and the Expression as

=sum({$<YourDateField={"=YourDateField>=Max(total Floor(YourDateField))-14"}>}SALES)

Does this making sense to you?

Regards,

-Basav

Re: Rolling 14 days data

Darrenkeirl — Tue, 30 Jun 2020 07:45:54 GMT

Morning Basav

I am well thank you. Yourself?

I am very new at Qlikview so hopefully the below provides you with answers
to your questions

The dimensions I am using are:
CalenderDay & Full Name

The Expression I am using are are:
[TotalExternalInboundAnswered] & [TotalExternalInboundAnswered] added
together

I am also adding some complexity to the expression by specifying the people
I wish to return result for by including the below:

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"},

Any help you can provide would be most gratefully recieved

Re: Rolling 14 days data

Darrenkeirl — Tue, 30 Jun 2020 11:20:54 GMT

Good afternoon Vamsee.

Thanks for your advice. Works perfectly by the look of it however I now
need to make it a little more complex by specifying some specific criteria
in the expression (see below)

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"},

Could you advise how I do this?

Re: Rolling 14 days data

vamsee — Tue, 30 Jun 2020 13:48:13 GMT

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'}, CalenderDay ={">=$(=CalenderDay(Max(Date)-14))<=$(=CalenderDay(Max(Date)))"} >} [TotalExternalInboundAnswered]+[TotalExternalInboundAnswered])

Re: Rolling 14 days data

Darrenkeirl — Tue, 30 Jun 2020 13:59:54 GMT

Thanks for the help however I am still getting all of the data (not just
the last 14 days)

below is the return as a table data that i get
CalendarDay Full Name Total External Inbound Calls Answered
1 Darren Keirl 102.00000000000000
1 Julita Wakefield 2.00000000000000
1 Lisa Lewer 102.00000000000000
1 Liz Halliwell 16.00000000000000
1 Sandra Searle 166.00000000000000
2 Darren Keirl 56.00000000000000
2 Emma Hares 16.00000000000000
2 Julita Wakefield 24.00000000000000
2 Lisa Lewer 80.00000000000000
2 Liz Halliwell 74.00000000000000
2 Sandra Searle 126.00000000000000
3 Darren Keirl 108.00000000000000
3 Emma Hares 60.00000000000000
3 Julita Wakefield 102.00000000000000
3 Lisa Lewer 138.00000000000000
3 Sandra Searle 166.00000000000000
4 Darren Keirl 82.00000000000000
4 Emma Hares 58.00000000000000
4 Julita Wakefield 62.00000000000000
4 Lisa Lewer 104.00000000000000
4 Liz Halliwell 54.00000000000000
4 Sandra Searle 148.00000000000000
5 Darren Keirl 84.00000000000000
5 Emma Hares 60.00000000000000
5 Julita Wakefield 68.00000000000000
5 Lisa Lewer 132.00000000000000
5 Liz Halliwell 32.00000000000000
5 Sandra Searle 136.00000000000000
6 Darren Keirl 100.00000000000000
6 Emma Hares 90.00000000000000
6 Julita Wakefield 104.00000000000000
6 Lisa Lewer 126.00000000000000
6 Liz Halliwell 18.00000000000000
6 Sandra Searle 140.00000000000000
7 Darren Keirl 66.00000000000000
7 Emma Hares 46.00000000000000
7 Julita Wakefield 64.00000000000000
7 Lisa Lewer 94.00000000000000
7 Liz Halliwell 54.00000000000000
7 Sandra Searle 126.00000000000000
8 Darren Keirl 76.00000000000000
8 Emma Hares 40.00000000000000
8 Julita Wakefield 42.00000000000000
8 Lisa Lewer 74.00000000000000
8 Liz Halliwell 26.00000000000000
8 Sandra Searle 132.00000000000000
9 Darren Keirl 56.00000000000000
9 Emma Hares 46.00000000000000
9 Julita Wakefield 74.00000000000000
9 Lisa Lewer 100.00000000000000
9 Liz Halliwell 110.00000000000000
9 Sandra Searle 126.00000000000000
10 Darren Keirl 52.00000000000000
10 Emma Hares 66.00000000000000
10 Julita Wakefield 146.00000000000000
10 Lisa Lewer 104.00000000000000
10 Liz Halliwell 20.00000000000000
10 Sandra Searle 74.00000000000000
11 Darren Keirl 100.00000000000000
11 Emma Hares 56.00000000000000
11 Julita Wakefield 64.00000000000000
11 Lisa Lewer 56.00000000000000
11 Liz Halliwell 48.00000000000000
11 Sandra Searle 146.00000000000000
12 Darren Keirl 74.00000000000000
12 Emma Hares 54.00000000000000
12 Julita Wakefield 90.00000000000000
12 Lisa Lewer 104.00000000000000
12 Liz Halliwell 88.00000000000000
12 Sandra Searle 128.00000000000000
13 Darren Keirl 80.00000000000000
13 Emma Hares 68.00000000000000
13 Julita Wakefield 72.00000000000000
13 Lisa Lewer 94.00000000000000
13 Liz Halliwell 26.00000000000000
13 Sandra Searle 76.00000000000000
14 Darren Keirl 90.00000000000000
14 Emma Hares 34.00000000000000
14 Julita Wakefield 46.00000000000000
14 Lisa Lewer 58.00000000000000
14 Liz Halliwell 58.00000000000000
14 Sandra Searle 100.00000000000000
15 Darren Keirl 44.00000000000000
15 Emma Hares 22.00000000000000
15 Julita Wakefield 52.00000000000000
15 Lisa Lewer 108.00000000000000
15 Liz Halliwell 24.00000000000000
15 Sandra Searle 150.00000000000000
16 Darren Keirl 36.00000000000000
16 Emma Hares 36.00000000000000
16 Julita Wakefield 78.00000000000000
16 Lisa Lewer 88.00000000000000
16 Liz Halliwell 102.00000000000000
16 Sandra Searle 104.00000000000000
17 Darren Keirl 80.00000000000000
17 Emma Hares 74.00000000000000
17 Julita Wakefield 140.00000000000000
17 Lisa Lewer 108.00000000000000
17 Liz Halliwell 14.00000000000000
17 Sandra Searle 100.00000000000000
18 Darren Keirl 80.00000000000000
18 Emma Hares 56.00000000000000
18 Julita Wakefield 126.00000000000000
18 Lisa Lewer 112.00000000000000
18 Liz Halliwell 46.00000000000000
18 Sandra Searle 146.00000000000000
19 Darren Keirl 86.00000000000000
19 Emma Hares 70.00000000000000
19 Julita Wakefield 56.00000000000000
19 Lisa Lewer 124.00000000000000
19 Liz Halliwell 38.00000000000000
19 Sandra Searle 150.00000000000000
20 Darren Keirl 88.00000000000000
20 Emma Hares 40.00000000000000
20 Julita Wakefield 82.00000000000000
20 Lisa Lewer 148.00000000000000
20 Liz Halliwell 66.00000000000000
20 Sandra Searle 170.00000000000000
21 Darren Keirl 90.00000000000000
21 Emma Hares 16.00000000000000
21 Julita Wakefield 40.00000000000000
21 Lisa Lewer 98.00000000000000
21 Liz Halliwell 52.00000000000000
21 Sandra Searle 100.00000000000000
22 Darren Keirl 74.00000000000000
22 Emma Hares 36.00000000000000
22 Julita Wakefield 24.00000000000000
22 Lisa Lewer 132.00000000000000
22 Liz Halliwell 50.00000000000000
22 Sandra Searle 196.00000000000000
23 Darren Keirl 56.00000000000000
23 Emma Hares 40.00000000000000
23 Julita Wakefield 34.00000000000000
23 Lisa Lewer 88.00000000000000
23 Liz Halliwell 58.00000000000000
23 Sandra Searle 118.00000000000000
24 Darren Keirl 104.00000000000000
24 Emma Hares 78.00000000000000
24 Julita Wakefield 102.00000000000000
24 Lisa Lewer 152.00000000000000
24 Liz Halliwell 30.00000000000000
24 Sandra Searle 92.00000000000000
25 Darren Keirl 60.00000000000000
25 Emma Hares 54.00000000000000
25 Julita Wakefield 88.00000000000000
25 Lisa Lewer 84.00000000000000
25 Liz Halliwell 40.00000000000000
25 Sandra Searle 84.00000000000000
26 Darren Keirl 124.00000000000000
26 Emma Hares 46.00000000000000
26 Julita Wakefield 104.00000000000000
26 Lisa Lewer 140.00000000000000
26 Liz Halliwell 44.00000000000000
26 Sandra Searle 146.00000000000000
27 Darren Keirl 106.00000000000000
27 Emma Hares 68.00000000000000
27 Julita Wakefield 58.00000000000000
27 Lisa Lewer 148.00000000000000
27 Liz Halliwell 92.00000000000000
27 Sandra Searle 146.00000000000000
28 Darren Keirl 64.00000000000000
28 Emma Hares 42.00000000000000
28 Julita Wakefield 64.00000000000000
28 Lisa Lewer 126.00000000000000
28 Liz Halliwell 76.00000000000000
28 Sandra Searle 90.00000000000000
29 Darren Keirl 136.00000000000000
29 Emma Hares 54.00000000000000
29 Julita Wakefield 94.00000000000000
29 Lisa Lewer 154.00000000000000
29 Liz Halliwell 32.00000000000000
29 Sandra Searle 144.00000000000000
30 Darren Keirl 42.00000000000000
30 Emma Hares 22.00000000000000
30 Julita Wakefield 26.00000000000000
30 Lisa Lewer 76.00000000000000
30 Liz Halliwell 32.00000000000000
30 Sandra Searle 80.00000000000000
31 Darren Keirl 34.00000000000000
31 Emma Hares 16.00000000000000
31 Julita Wakefield 20.00000000000000
31 Lisa Lewer 48.00000000000000
31 Liz Halliwell 20.00000000000000
31 Sandra Searle 76.00000000000000

Re: Rolling 14 days data

vamsee — Tue, 30 Jun 2020 14:33:35 GMT

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

Re: Rolling 14 days data

Darrenkeirl — Tue, 30 Jun 2020 14:38:54 GMT

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?

Re: Rolling 14 days data

vamsee — Tue, 30 Jun 2020 14:46:57 GMT

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)

Re: Rolling 14 days data

Darrenkeirl — Tue, 30 Jun 2020 14:52:54 GMT

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

Re: Rolling 14 days data

vamsee — Tue, 30 Jun 2020 15:23:41 GMT

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.

Re: Rolling 14 days data

Darrenkeirl — Tue, 30 Jun 2020 15:39:54 GMT

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

Re: Rolling 14 days data

Darrenkeirl — Tue, 30 Jun 2020 16:04:44 GMT

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?

Re: Rolling 14 days data

vamsee — Tue, 30 Jun 2020 16:15:56 GMT

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

Re: Rolling 14 days data

Darrenkeirl — Tue, 30 Jun 2020 16:29:01 GMT

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

Re: Rolling 14 days data

vamsee — Tue, 30 Jun 2020 17:16:00 GMT

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