Dashboard excel monthwise sum

[former_member221192]

HI,

Data is coming dad wise ,i have calculate monthwise,find the below example

date               Amount

01/04/2014     56

02/04/2014     45

03/06/2014     30

04/06/2014     10

i nedd like this each month wise sum

04.2014     101

06.2014     40

find the screen shot,Actually data will Dynamically,according to that inform the sumif functionh

how to do ,guide me

[Former Member · Accepted solution]

Here you can calculate the month number by formula :- =Month() for the column "date"

then use the formula: =SUMIF($G$4:$G$15,G4,$B$4:$B$15)    {check the snapshot and manage the formula accordingly}

                               =SUMIF($G$4:$G$15,G5,$B$4:$B$15)

                                  ..

                                  ..

                               =SUMIF($G$4:$G$15,G15,$B$4:$B$15)

this will calculate the amount you require.  (You just need to drag the formula for the entire column)

Now,

if you want for every month then that also can be done:-

just edit the formula :-- =SUMIF($G$4:$G$15,4,$B$4:$B$15)

{ here =SUMIF($G$4:$G$15,4,$B$4:$B$15) 4 stands for april.

for "may" month the formula is =SUMIF($G$4:$G$15,5,$B$4:$B$15) 5 Stands for may. }

similarly for other months.

please forward you email if you want the excel for better understanding.

revert back if any doubt.

Regards,

Yash

[former_member221192 · Accepted solution]

we can't fix the month,it is dynamic,whatever month it will come

[Former Member · Accepted solution]

it will be dynamic. it can handle any month.

[former_member221192 · Accepted solution]

here you have mentioned 4,5 month numer for calculating month sum such April,may,june,july

[Former Member]

You can also try using following formula at excel level.

B1=TEXT(A1,"mmmm") where A1 will be the required date.

and then apply formula mentioned in following link to the column

http://stackoverflow.com/questions/19669814/how-to-sum-parts-of-a-column-which-have-same-text-value-...

hope that helps.

Regards

Supriya

[Former Member]

Hi Ganapathy,

You can convert your date column into text using:-  =IF(A2="","",TEXT(A2,"mmmm yyyy")) ; where A2 is the first date. The for getting the measure data, you can use the INDEX formula as:- =INDEX(A2:B6,1,2) . 

Then use the excel functionality as -  DATA --> CONSOLIDATION into some other column and get the result as below-  P.S- for using the consolidation feature we have to take the column headers in range as well, otherwise it won't consolidate

Hope it solves your problem.

Regards,

Rohini

[saurabh_sonawane]

Hi,

what is your source (back-end)

[former_member221192]

SAP BW Netweaver BW connection

[saurabh_sonawane]

hi

just create a one more query in dashboard  which contain the cal month and amount only.

note cal month should be derived from the posting date in BI at transformation level.

[former_member182541]

To cook this in excel level is possible but need lot of formula and need to be checked whether its giving the exact result even at the level of dynamic data. I would suggest you right the logic in the source side and to bring the data into the dashboard, which makes your life easier.

[Former Member]

1. If data is coming from universe, please try to add calculated column for Month in Data Foundation, and Sum Measure for Amount in Business Layer, which makes job easy to use them directly in the dashboard.

2. Second option, need to work in excel.

a) Col C: Extract Month

b) Col 😧 Hard Code Months

c) Col E: Use SUMIF function on columns C,B with criteria D.

*If you don't want to show up rows where you don't have data, we need to work little bit on the formula or on the component configuration.

** Alternate is to use Lookup function, but performance will go down, and will not work on Mobile.

Let me know if you are planning to use Excel option and need changes, or changes to Universe, I will be happy to help you.

Thanks,

Sagar

[former_member221192]

Hi,

I am not using universe,directlly using SAP BW Netweaver Connection,guide

one thing data will come dynamically

[Former Member]

Ganapathy,

Most of us suggesting about using SUMIF function is correct, and it will handle dynamic data.

I'm guessing the problem might be the format of the data you are getting.

If you can extract data (couple of rows) into excel and attach it here or send it to us, we will be able to give you exact formula.

Thanks,

Sagar

[former_member197751]

Hi Ganapathy,

Can i know from where you are pulling data in Dashboard

Thanks and Regards

Sateesh

[former_member221192]

SAP BW Netwear connection,Data will come dynamically, based on from date and to date,

if i give from date is 01/01/2013 and to date id 05/06/2013

totally 6 month data will come

Posting date  Amount

01/01/2013     23

02/02/2013     45

03/06//2014     11

04/06/2014     10

i have to calculate monthwise  casolitation