Pulling matching data from a pivot

[Guest]

Hello,

I have a pivot that changes when you select a certain criteria in the pivot,
meaning sometimes there will be some rows and columns that are the disapear.

Then I have a standardized table, that always has the same data. I want to
be able to pull the values within the pivot to match the correlating standard
data.

pivot example:
Date 1 Date 2 Date 4 Date 6
A 1
B 3
D 2 4
F 4 2

standard table:
Date 1 Date 2 Date 3 Date 4 Date 5 Date 6 Date 7
A 1
B 3
C
D 2 4
E
F 4 2

 

[Roger Govier]

Hi

Double click on your Row field header in the PT>select Include Items with no
data
Your tables will now be identical in layout.

 

[Guest]

Roger,

Awesome! But, I still have a problem.. those dates are every day in the
year. When I click the entry to show items with no data, it shows an excess
of 360 days. I have a filter in the page that allows me to only show the
previous week. Any idea on what to do to get just the previous weeks data in
my pivot??

 

[Guest]

HAHA!

I figured it out.

=IF(AND(H$17=H$27,$A28=$A18),H18,IF(AND(G$17=H$27,$A28=$A18),G18,IF(AND(F$17=H$27,$A28=$A18),F18,IF(AND(E$17=H$27,$A28=$A18),E18,IF(AND(D$17=H$27,$A28=$A18),D18,IF(AND(C$17=H$27,$A28=$A18),C18,IF(AND(B$17=H$27,$A28=$A18),B18,"0")))))))

If you know a better way, please tell me.

 

[Roger Govier]

Hi

You could create an additional copy of your date column and call it Date2.
Drag Date2 to the Row area
Right click on the field>Group and Show Detail>Group>highlight Days>Number
of days>7
Now drag Date 2 to the Page area
Use the dropdown on Page to select the week you want.

Note:
You cannot Group data in a Page field, that is why you need to drag it to
the Row area first, do the grouping, then drag to the Page area.

 

[Guest]

I tried,

It will not show all prior 7 days with the filter.

Thank you for helping

 

[Roger Govier]

Hi

Another suggestion then.
Assuming Date is in Column A and Date2 is in column B
In A2 enter
=TEXT(*B2,"ddd")
Copy down

Do the grouping on Date2 as before into 7 day grouping, but then drag to the
Column area instead on the Page area.
Click the dropdown on the Column field, and just select whichever weeks you
wish to display and you will have them side by side.