• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Customized Pivot table

narsing rao

narsing rao

Member
Hi Pcosta,

Hope your doing good....need your suggestions in creating customized Pivot table

upload_2017-3-13_11-12-47.png

i you see above table i have upto A to P columns, i want to create a Pivot table in below given style.

upload_2017-3-13_11-14-41.png


i know that data is different and only dates are mentioned can i insert yesterday , This month and This Financial Quarter in the column fields manually.

please let me know is there any possibility in macros i can do it in same style.

Thanks,
Narsing Rao
 
This is best done via PowerPivot DAX measures using calculated/manually created date dimension table.

Do you have access to it?

In either case, I'd recommend uploading sample workbook so that helpers need not recreate your set up from image.
 
Hi I don't have the PowerPoint DAX... Can you share the link so that I can download it.

please find the sample file.
 

Attachments

  • Pivot.xlsx
    16.5 KB · Views: 3
by mistakenly I mentioned powerpoint sorry for that....we have 2013 ....and I don't know about licensing as we are using it in office
 
Ok, so PowerPivot isn't likely an option then.

Probably easiest to set up dynamic named range and use formula. Rather than using vba. I'm going into meeting now, but will look at it later.
 
Yes, why i have attached above sheet is because ...i have around 8 reports from these 8 sheets i will make Summary report this summary report is all of Pivot table which looks something like below.

upload_2017-3-14_9-56-15.png

what i have pasted previously is only one part ...but all 8 reports looks something like above ..what i am trying is i will attache pivot one by one below it....in all reports severity and date fields are same but some column may change.
 
in some reports we consider logged date , in some reports we may consider closed date ( as mentioned in calls closed ), what you have uploaded seems to be right but in one quarter we have only three months( JAN,FEB,MAR) and the quarter should be considered from Jan 1st to till date like wise next quarter would be April 1st to till date.
 
but in one quarter we have only three months...
Click to expand...
Isn't my lookup table set up for 3 months quareter? It will consider any dates within that three months time-frame as in the quarter.

Also, you can just adjust columns used in the formula (and formula itself where needed) to set up entire report. No need for copy and paste different pivots.

Pivots are meant to report based on structured grouping and not meant for what you describe.
 
Hi,
when i copied my data into sheet1 its not showing values in table ..its just showing Ref error...i thing i have done something wrong ..can you explain how do i correct it.
 
Just remove all rows from existing table. Then copy your data excluding header into data body range of the table.
 
Hi, coming back to your Pivot table sheet .. can you let me know what you have done in column N and O , Because when i tried to changed the values of month in ascending order the count is also changing......and in the formla what is table2 ??

Code: 
COUNTIFS(Table2[Severity],COLUMN(B1),Table2 [Lodgedate]
 
Table2 is data in sheet1. To make range dynamic, I converted data range to structured table. You can check the range & detail using "Name Manager" found in Formulas tab in ribbon tool.
upload_2017-3-17_7-40-39.png

N:O is just the lookup table assigning each month to corresponding quarter.

Count should not change as long as both N & O are sorted as group.
 
Hi,
i tried all sorts of other formulas but getting same error #VLAUE!
now trying to apply your formula to my other sheets but i am getting TABLE 2 NAME showing as fixed length row ..am i correct ..does it expands according to the increased number of rows. can you put some lite on why am getting value erroe i tried changing number to integer and words to character but still have same problem

like:

=COUNTIFS(Sheet2!G:G,"sev-1",Sheet2!J:J,">="&DATEVALUE("1-Mar-2017"),Sheet2!J:J,"<"&DATEVALUE("31-Mar-2017"))

=COUNTIFS(Sheet2!G:G,"sev-1",Sheet2!J:J,">="&DATE(2017,3,1),Sheet2!J:J,"<"&DATE(2017,3,31))


qtr
=COUNTIFS(A:A,"> ="&DATE(YEAR(TODAY()),MONTH(TODAY())-3,DAY(TODAY())),B:B,"sev-1")


year
=COUNTIFS(A:A,"> ="&DATE(YEAR(TODAY())-1,MONTH(TODAY()),DAY(TODAY())),B:B,"sev-1")

all giving same error.


please help in this...
 
So you only have numeric value stored in Column G of sheet2.

But you are checking if it matches string value like "sev-1".

This will result in value being always 0.

Also, you don't have any #Value! error in your uploaded workbook.

Each sheets in your attached has different structure and column (for an example, Sheet4 has Column E which contains Severity, not in Column G). Also some table has Severity not as numeric value but as string etc.

You'll need to clean up your data if you want to keep formula consistent.
 
Hi,,


Can you suggest me any pivot table approach where i can get at least Severity count for all the sheets so that i can copy the same to summary sheet??
 
Hi,

I have recreated entire worksheet ...and now i am able to insert the formulas and get the count ....but only problem is Today()-1 is not working

Like :
=COUNTIFS('Calls Breached'!H:H,"Sev-1",'Calls Closed'!I:I,TODAY()-1)
=COUNTIFS(Sheet2!H:H,"Sev-1",'Calls Closed'!I:I,TODAY()-1)

its giving only 0 value

all other formulas are working without any problem.

any Suggestions ??
 
Without looking at what's actually contained in columns referenced. I can't really answer you.

But likely caused by one of following:
1. Column H only contains numeric value, but you are checking with stirng
Or
2. Column I doesn't have date value, but stirng
 
You must log in or register to reply here.
Back
Top