Webintelligence - Calendar format

[vishal_p3]

Hello All,

I have a requirement where I need to show the forecasts of sales and revenue for the coming days of the month which the data is coming from BEx query as shown in the table(Figure 1).

I would require to represent the same in a calendar format shown in the image attached.

Please suggest how to achieve.

Figure 1:

Datesales ForecastRevenue Forecast5th March200$100$6th March250$110$7th March240$115$8th March255$100$9th March200$120$10th March180$100$11th March200$90$---31st March220$120$

Figure 2: Attached

[vishal_p3]

Attaching the table details as an image.image-1.png

[Tom_N8]

Hi Vishal,

This is relatively easy to achieve from a formatting perspective.

The hardest part is the formatting piece. Start with creating a second query against an integer range that will return values from 1 to 6. I have called this [DayOffset] in my query. The values are needed to dynamically size the "hidden" table that sits in front of the first table.

Once you have run both your queries, only three variables need to be created:

1. [Day Name] which is a detail object associated to the [Date] dimension: =DayName([Date])
2. Next, create a measure variable. I called it [Marker] =DaysBetween((Min(LastDayOfWeek([Date]))In Report);Min([Date])) You will need this to determine filter values for each individual table block consting of seven week days:
3. Finally, create another variable that is needed for the dynamic dummy table that shifts your first table block according to which day your calendar month starts on. I called it [Dummy Table Adjust] and it has the following syntax: =If([Marker]=-1) Then (([DayOffset] Where([DayOffset] InList(1;2;3;4;5;6))) ForEach([DayOffset])) ElseIf([Marker]=-2) Then (([DayOffset] Where([DayOffset] InList(1;2;3;4;5))) ForEach([DayOffset])) ElseIf([Marker]=-3) Then (([DayOffset] Where([DayOffset] InList(1;2;3;4))) ForEach([DayOffset])) ElseIf([Marker]=-4) Then (([DayOffset] Where([DayOffset] InList(1;2;3))) ForEach([DayOffset])) ElseIf([Marker]=-5) Then (([DayOffset] Where([DayOffset] InList(1;2))) ForEach([DayOffset])) ElseIf([Marker]=-6) Then (([DayOffset] Where([DayOffset] InList(1))) ForEach([DayOffset]))

Once you have created the variables, add them to your default table block. It will make it a little easier to begin with:

Next, apply a filter to the table block: [Marker] Between 0 And 6

Now, apply your first formatting to the table. Start with turning the table into a cross table and adding two rows above the header:

Add "Sales" and "Forecast" to both top row header cells, then move [Day Name] from the vertical axis to header row 2. Merge both cells in rows 2 and 3, respectively. Move your Revenue and Sales Forecast measures into their respective body axis cells and delete the empty column from the horizonatl axis. Note that your vertical axis will now be empty. Remove the column via right-clicking:

Your first table block now looks like this:

Now, apply some more formatting. Select all cells and go to Format Cell:

• In the General section untick the Autofit options and select 2 cm for width and 0.6 cm for Height
• In Alignment reduce the cell padding to 0 cm for all four sides. Choose Center for vertical and horizontal alignment
• In Appearance select No Color for the background
• Apply further individual formatting to your cells to match the desired style

Next, you need to add a custom sort to [Day Name] in the table block to achieve the desired start day and date. In this case we start our weeks on Sunday. Therefore create your custom sort in ascending order with

• Sunday
• Monday
• Tuesday
• Wednesday
• Thursday
• Friday
• Saturday

Format the table layout to move it horizontally to 0.5 cm from the Left of Report, and vertically 0.5 cm from the Top of Report.

We are now getting to the stage where you simply duplicate the table blocks (copy and paste), and aligning them right underneath one another before adjusting the filters. You will need six table blocks in total. Once you have applied the formatting to each block you will need to adjust the table filters for each block as follows:

• Block 1: [Marker] Less Than 0
• Block 2: [Marker] Between 0 And 6
• Block 3: [Marker] Between 7 And 13
• Block 4: [Marker] Between 14 And 20
• Block 5: [Marker] Between 21 And 27
• Block 6: [Marker] Greater Than 27

Your report now looks like this:

We are now getting very close. Next, you need to create the dummy table to adjust the table alignment of your first block. Copy one of the table blocks and clear the contents from all cells as well as the filter that sits on the copied block. Add [DayOffset] from your second query to the block and then apply a filter on table: [Dummy Table Adjust] Is not null:

Readjust the width of the table cells in the dummy block to 2 cm and then adjust the positioning of the dummy table block to place it in front of your top table block, i.e. Format Table to move it horizontally to 0.5 cm from the Left of Report, and vertically 0.0 cm from the Top of Report. Then adjust your top table block position to 0 cm from the Right edge of the dummy table.

You are now almost there!

Apply the final touches by deleting all but the [DayOffset] row and one other row from the dummy table. Then remove the borders from the cells, remove any background colors and set the font color to White. Your top table block is now perfectly aligned. No matter what day your month starts, the top calendar block will always start in perfect alignment:

Last but not least apply "Hide when empty" General Display formatting to you first, last and the dummy table blocks. Then go to Page Setup, change the Page mode to Landscape and adjust your margins to fit the full "calendar" on one page. With the cell sized in this example reduce the left and right margins to 0.3cm.

Voila!

Kind regards,

Tom

[vishal_p3]

Hello Tom,

Thank you for the detailed explanation.

I need a clarification on how & where the integer range [DayOffset] should get created and set from 1 to 6? do we require to create a new BEx qurey?

Thanks!