Value disappeared when I use combined tables

[Dillina]

Hello guys
I have a problem with pivot tables, ONLY when I work with two combined tables.
I do this:
Sheet 1: I create a table
Sheet 2: I create the second table
Click in the second table, enter Pivot Table, put the flag in "Add this data in the Data Model"

I call Table Pivot the 3 sheet:
I prepare my pivot table but when in Values I put the value "start" (which contains some dates), to me it appears "count" (I do not know the term in English, sorry) and I can not choose "product".

This happens to me ONLY when I try to work with two combined tables
Someone could tell me why and how can I do?
Grazie mille!!!
Dilly

 

[GraH - Guido]

Ciao Dillina,

I think you might miss the step of creating a relationship between both tables. That is mandatory in a data model.
Unfortunately I'm stuck with Excel 2010 for the moment, so I cannot even access your data model.
If you want the dates to appear as dates in a column, you need to slide them in the section "rows" and not in values, where you do counts, sums, etc.
Could that already help you a bit further?

 

[Dillina]

Hello Guido and thank you for your answer
Sorry for my English.
(I take this opportunity to congratulate you on the bike chart because it is wonderful and I love graphics.)

For my file instead ... I do not understand why if I work only with a table, the value I want is present and in the value column I can see the dates.
The problem is that I have to make a graph and I need dates in that column.

I do not know if I'm wrong when I connect the two tables ... but I do not think ...
If I have no alternative I have to abort to work with combined tables

 

[GraH - Guido]

Dillina, that is kind of you to say. Thank you. Don't be sorry, my English isn't that great neither. And my Italian is lousy, as in non-existing.

I've opened your file with version 2016 and I can see your data model now.


As I suspected there is no link between those tables. Otherwise there would be connections from table to table to show the relationships.
Furthermore in Power Pivot (which by default you are using with a data model), you can only connect a transaction table with a second lookup table if in the lookup table you do not have repeated keys. In your example the link or key could be animal name, but in all 3 tables the names are repeated more then once. That cannot work. The data should be normalized and standardized before. Sorry for those technical terms. It means that the data must be organized in such way you actually can combine the tables and benefit from the data model.
I believe you are trying to use a feature here of which you do not know the basics.

The date is not showing as date since it in the value section and not in the row section. See the attached file.
I do not understand the thing about "product" not showing up. I do not see a field name like that.
I hope it is a step in the right direction.

 

[Dillina]

Hi Guido
... I thought you were Italian !!!! : D

1: You said a great truth: "I believe you are trying to use a feature here of which you do not know the basics."

2: Power Pivot ???? Never use Power pivot. I do not have Power Pivot in Excel 2013.

Is not possible use two simple pivot tables (no Power) and work with those?

I'll give you an example in another sheet of my file that I'll call "Simple Pivot" to show you the voice "product" that I need. And you will see that in the field of values we will find "product".

I wanted to combine two pivots tables (simple) because I did not want to make a single table with the data of the first and the data of the second.

Did I explain myself?
I can not see your file !!!

 

[GraH - Guido]

Not the first one here who made that assumption. No I'm Belgian... Where Guido is a very common name. Nicknamed Guido Bontempi. Because I'm a cyclist and I had a rather good sprint when I was younger. Hence the biker chart...

Yes do have Power Pivot, it is in Excel 2013 and you activate it when you use a data model. Once you are in there you are not using legacy pivots. And for the sake of it, you also have Power Query...

Will look at the new file a bit later... But I understand it, you actually do not need a data model.

 

[Dillina]

Actually Guido, I just realized that I have another problem
Excel is full of surprises, especially for those who do not know it well and I apologize for this.

The post was born to know why when I combined two tables did not appear to me this famous "product".

But now I have seen that I can not do what I wanted even if I use 1 table.

My final goal was to create a Gantt Chart with two categories using 1 or 2 simple tables. The reason to use 2 tables is because I have dates that do not coincide in the TREA and BS Tab.
(Not using vba of course)

I try to give an example to understand if it is I who can not do it.
If it is very difficult do not worry, I open another post with a different title.
I'm sorry but you think you have only one problem and going forward, another 10 are coming

 

[GraH - Guido]

Call problems learning opportunities... and already the problem is gone. But there comes challenge...
I'm starting to think that Power Query, or even some formulas could make the table you are after, on which you can make the chart.
Can you therefore make a manual example of how this table should look like according to you? I don't see the required pattern yet to be honest. I do not understand the data I see.
I'm guessing the link between those 2 tables are actually the dates and not especially the animal names. So you would need a third calendar table, having all dates of the year, to plot the data against. Am I getting closer?
PS: Now also understanding that "product" was a missing operator value in the pivot you were making. Because it was a Power Pivot... But I still do not understand why you would place the dates in the Values area and not in the Rows area of the pivot.

 

[Dillina]

Thanks Guido for your help and your patience.
For two years I'm challenging Excel, when I knew only use the sum formula.
My problems are two: I do not know the pivots well, I do not speak English to explain how I would like to

In this new file I inserted in the sheet "Chart no Pivot". This is the graph that I always do manually for each animal.

With the pivot table I was hoping to be able to automate everything.
In my head I imagined .... Click..change animal and Excel (ora Chandoo) does the magic!
... but I think it's not as easy as it was in my head.

The dates of the two tables are always different.
I hope I have been clearer.

I'm sure I'm missing some steps

 

[GraH - Guido]

Hi Dilly,

A new world opened to me when 18 years ago my manager back then showed me sumif () and countif(). A lot has happened since then. It's very recognizable what you say about knowing only about SUM.
Give me some more time, but I'm close to making the chart responsive to the slicer. Your example really helped me to understand what you want to accomplish. Thanks for that one. I made it too difficult for the moment, so let me clean-up the mess and give you a clean workbook in return. Hopefully with a solution you can replicate. I'll document it the best way I can. But I need time to do that.

 

[Dillina]

Dear Guido,
Do not worry about the time. I will be very curious to see what you do, but above all to learn new things. I love graphics and this type of graphic for me is very, very important. I want to learn how to do it.

Excel is like a drug....more you know it, more you pretend and more you like it.
If I find a solution alone (but I doubt because I've been trying for 7 months) ...I will write it here.
Thanks for your help.
Kiss
Dilly

 

[GraH - Guido]

Like this?

 

[Dillina]

Hi Guido ,
I downloaded the file. Now it's me that I need time to understand it.
Google translator will help me to translate the English of the DOC sheet better and my mind will have to understand the formulas well and if it is really the graph I was looking for a long time.
Apparently it looks like "him" !!!
Thanks for the time lost for me.
Tomorrow I'll tell you something ... tonight I study !!
Kiss
Dilly

 

[Dillina]

Hi Guido
I have not forgotten about you ...
Today I looked at the file ... I had very little time available (for work)
I'm trying to understand but I must admit that I did not understand much, but I think it was because I did not have much time available.
Tomorrow I will watch it with more time and more attention

 

[Dillina]

Buongiorno Guido
I must admit that it is very difficult for me to understand the mechanism. Maybe I miss some basic passage.
But I can tell you that you're a genius because that's what I wanted.
Let me test several times and I am sure that I will understand better what you have explained to me.
I have only the last one question ... (you do not kill me ). I tried but I could not.
Is it possible to have a Slicer with "Cherries Pears Apples" so that I can choose what I want to see in the chart?

I love Excel every day more ... and too!!!!

 

[GraH - Guido]

This isn't magic, it's Excel. The book of Bob Umlas has the inverse title, but in the end the "magic" is just a combination of basic Excel stuff. I understand you can be overwhelmed by it: a lot goes on in the way I made it work.
The mechanism is not too difficult. The slicer works on a simple pivot table. The counter formulas check if there is a selection made or not. If the selection is made the chart table gets populated based on selection value. The dynamic chart ranges are made with offset-function in the name manager.

So the features you might want to check out a bit are:
- virtual formulas via name manager
- indirect and offset function
- aggregate function :http://masterexcel.net/tag/aggregate/ trick 1346.
- table reference style: https://www.excelcampus.com/tips/absolute-formula-references-excel-structured-table/
- dynamic (chart) ranges: https://chandoo.org/wp/2009/10/15/dynamic-chart-data-series/

 

[Dillina]

Good morning Guido and Happy Easter!
My head is very hard to understand, but I know that I'm close and I know that in a week I'll tell myself ..... What a "idiot" I've been not to understand right away!

I'm transforming my file with your advice where there are real data and I like it very much.

The links you gave me keep them as jewels and I was watching them, but I did not understand if that's what I want (in the second step)

Now I have another "problem" that often happened to me and I could not solve it.
I will open another post because the theme is different.

You have solved this post brilliantly.
Grazie Mille di cuore!!!! <3