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]

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

[vishal_p3]

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

[Tom_N8]

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