Application Insights: Display Heatmap of Page Views per calendar week

Recently I wanted to display a summary of application activity per calendar week, and preferably render it as a Heatmap.

It took me longer than I care to admit, so I'm posting my results here. Hopefully it will save you some time =)

Query Application Insights Logs

Let's make a quick test first.

1. Sign in to the Azure portal.
2. Under the Monitoring section, select Logs.
3. Paste the following in the query editor and Run ```kusto

let start = startofweek(ago(1h));
let end= endofweek(now());
let dow = dynamic(["Sun", "Mon", "Tue", "Wed", "Thu", "Fri", "Sat"]);
pageViews
| where timestamp >= start
| where client_Type == 'Browser'
| make-series Metric=sum(itemCount) default=0 on timestamp in range(start, end, 1d)
| mvexpand timestamp to typeof(datetime), Metric to typeof(long)
| extend WeekDay = toint(dayofweek(timestamp) / 1d), KW=week_of_year(timestamp)
| extend WeekDayName=tostring(dow[WeekDay])
| order by timestamp asc
| project-away timestamp,WeekDay
| evaluate pivot(WeekDayName, sum(Metric))
| project KW,column_ifexists("Mon",""),column_ifexists("Tue",""),column_ifexists("Wed",""),column_ifexists("Thu",""),column_ifexists("Fri",""),column_ifexists("Sat",""),column_ifexists("Sun","")

Depending on the data range defined in the first two lines, and presence of logs ingested to the Application Insights workspace, you may either get the _No results found_

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/1qati350w9yaubo3tcbu.png)

or a table aggregating page views across calendar weeks and weekdays

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/jumhf8nujrn8yectvag0.png)

### What happened here?
Few pieces here are important:

[`dayofweek()`](https://learn.microsoft.com/en-us/azure/data-explorer/kusto/query/dayofweekfunction): returns the integer number of days since the preceding Sunday, as a timespan. Sunday=0
`let dow = dynamic(["Sun", "Mon", "Tue", "Wed", "Thu", "Fri", "Sat"])` is a [dictionary](https://learn.microsoft.com/en-us/azure/data-explorer/kusto/query/scalar-data-types/dynamic) that allows translating weekday numbers into names
`| order by timestamp asc` makes sure that if your timespan is across two years, the last year's calendar week is displayed before this year (51,52,1,2)
`| project KW,column_ifexists("Mon","")` this is the part where we make sure that Monday is displayed as the first day of the week; `column_ifexists` ensures no errors are thrown in no data is returned

## Application Insights Workbook
We are not done yet. 
1. Under the **Monitoring** section, select **Workbooks** and create a new Workbook
1. Add Time Range parameter:
![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/8u74oqrqmutzs4nbrz0x.png)
1. Add query and paste the query you used before to the Query Editor. 
1. This time we can refer to the `TimeRange` parameter you just created
![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/0wm4qvv44wwx6ilmvji7.png)
1. To display the data in a **heatmap** format, change **Column Settings**
![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/7w67yvex11bvmxgl1hp4.png)
1. Save your work

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/iphol7z415mydnfarzaz.png)

## Gallery Template

You may find the Gallery Template [here](https://gist.github.com/kkazala/36d48bdae610089179c8058a97070cb6). 
To use it, create a new workbook, open it in **Edit** mode, switch to **Advanced Editor** 

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/ggea8txe3c2r8gzp505n.png)
paste the contents and apply