Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Darrenkeirl
Contributor
Contributor

Rolling 14 days data

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

35 Replies
vamsee
Specialist
Specialist

Try

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

 

Vegar
MVP
MVP

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

basav
Creator
Creator

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

Darrenkeirl
Contributor
Contributor
Author

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.
basav
Creator
Creator

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

 

 

Darrenkeirl
Contributor
Contributor
Author

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

Darrenkeirl
Contributor
Contributor
Author

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?


vamsee
Specialist
Specialist

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])
Darrenkeirl
Contributor
Contributor
Author

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