How to show empty months in dashboard ?

[Former Member]

Hello Gurus,

I have a reporting requirement to show the Sales Amount per month of current year and last year, as shown below (based on country).

Data is coming like this from the HANA view, and first 12 rows (Orange) are mapped for Last year sales and next 12 rows (green) are mapped for current year sales.

But, the issue is that all countries are not having data for all the 12 months, hence the chart will look wrong in that case as shown in the below example-

In the above example, there are no values for Jan to June, and hence the mapping will go wrong in the chart.

So, when the data from the hana view is coming only for valid months, is there any way to show the empty months also in the dashboard excel as shown below.

Thanks a lot

[Former Member · Accepted solution]

In your source do a vlookup from the target and for the corresponding matched value you would get the data for the missing ones there would be 0 (Zero) , by this way you can show months with 0 sales.

Hope this helps !!!

[Former Member]

Hi RJ,

Simplest way is to use match index on excel, you can use "ISERROR" function to avoid #N/A.

Here is an example how you can use it,

1.Index the vales you want to show

2.Match the Key word you are looking for in your case is "JAN".

3.Map the parameter of which you want the values.

=IF(ISERROR(INDEX($D$5:$D$320,MATCH($C6,$A$5:$A$320,0))),"",INDEX($D$5:$D$320,MATCH($C6,'$A$5:$A$320,0)))

Regards

Rahul

[former_member202257]

I am not sure if you have the option to 'Retrieve empty rows' while using the HANA view in the dashboard; this option is available under query browser though.

[Former Member]

Hi RJ

I have created an excel sheet that offers a solution to your issue (rename the extension from txt to xlsx)

Basically, map your xcelsius component to a different table which will contain the 12 months

You populate that table using match/index (avoid vlookup as it's a bad function in terms of performance).

The extra 24 month going from A26:A49 are in order to avoid the #N/A from the match function

Hope this will help you

Philippe

Xcelsius.txt.zip

[Former Member]

Thanks a lot Philippe,

Let me try with you excel

Thanks

RJ