Question Re: Webintelligence - Calendar format in Technology Q&A

Webintelligence - Calendar format

vishal_p3 — Tue, 05 Mar 2019 06:13:53 GMT

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

Re: Webintelligence - Calendar format

vishal_p3 — Tue, 05 Mar 2019 06:15:48 GMT

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

Re: Webintelligence - Calendar format

kohesco — Wed, 06 Mar 2019 08:56:28 GMT

with objects like 0calweek(rows) and 0weekday(columns) with keyfigure-strcuture you can create a matrix

Re: Webintelligence - Calendar format

vishal_p3 — Wed, 06 Mar 2019 09:43:30 GMT

Hi Hesters,

Can you please elaborate your comments.

Thanks!

Re: Webintelligence - Calendar format

kohesco — Wed, 06 Mar 2019 10:23:14 GMT

I see you have date in your CUBE/MULITPROVIDER so derive 0calweek and 0weekday from it.

0calweek will give you all the weeks of the year

0weekday will give you all the days of the week (1->7) (Mon-> Sun as text)

so this info should give you a matrix like

day 1 2 3 4 5 6 7

Week 1 X X X X X X X

Week 2 X X X X X X X

Week 3 X X X X X X X

Week 4 X X X X X X X

....

Re: Webintelligence - Calendar format

Tom_N8 — Wed, 06 Mar 2019 18:46:05 GMT

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:

[Day Name] which is a detail object associated to the [Date] dimension: =DayName([Date])
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:
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

Re: Webintelligence - Calendar format

vishal_p3 — Thu, 07 Mar 2019 13:38:50 GMT

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!

Re: Webintelligence - Calendar format

Tom_N8 — Thu, 07 Mar 2019 16:22:36 GMT

Dear Vishal,

You do not need to create another BEx Query for the second data provider. Instead you could run a second query on the same BEx provider, e.g. where you only pull the first six dates.

After you have run the query, create a new dimension variable [DayOffset] =ToNumber(Substr((FormatDate([Query 4].[Date];"dd/mm/yyyy"));2;1);"0") This will return values 1 to 6 which feed into the [Dummy Table Adjust] variable which we need for the filter on the dummy block.

Then right-click your dummy table and replace the old variable with the new variable via "Assign Data...".

Job done.

Kind regards,

Tom

Re: Webintelligence - Calendar format

vishal_p3 — Mon, 18 Mar 2019 02:43:49 GMT

Dear Tom,

Thank you for your detailed explanation was really appreciated.

Off late I realized that I have multiple values for few salesForecast or RevenueForecast(screenshots attached) and also there wouldn't be data continuous for all the dates of the month. However I tried using the "timeDim" function as a variable in Webi to get the continuous dates using "Date" field but later could not able to split in to blocks using the marker (0 to 6; 7 to 14;....).

Unfortunately, I don't have a "caldate"(which has all the dates 1st March through 31st March) coming from source which could have been used for the calendar format. Please suggest what best way if we could achieve this?

Thanks,

Vishal P

Re: Webintelligence - Calendar format

Tom_N8 — Wed, 20 Mar 2019 17:59:46 GMT

Hi Vishal,

You can use a text file or Excel sheet to store calendar dates if you have no other option. Stick it into the same folder you keep your report in (or a sub-folder). Then run a simple query against this file and merge the relevant date objects in WebI. You then will have to adjust the different variables to use the merged date object.

Kind regards,

Tom