Pivot Table/ Showing all the latest data

[#1]

Hi All,

I have 2 tables as given below:

table A

CNTXT NM	IND	SUB CNTXT NM	ROWS	Date Uploaded
A	AA	AAA	12	2-Sep-15
A	AA	BBB	13	2-Sep-15
A	AA	CCC	14	4-Sep-15
B	BB	DDD	15	5-Sep-15
B	BB	EEE	16	6-Sep-15
B	BB	FFF	16	7-Sep-15
A	AA	CCC	14	1-Sep-15

Table B

Day	Month	Year
1	Sep	2015
2	Sep	2015
3	Sep	2015
4	Sep	2015
5	Sep	2015
6	Sep	2015
7	Sep	2015

Table B is derived from my Date Uploaded field of Table A and is linked to Table A.

There are 4 filters on my dashboard viz. CNTXT NM, Day, Month, Year.

I have to show a pivot table with the above fields.

But my requirement says that when I make a selection of Day, Month and Year I have to always show all the CNTXT NM. That is, if i make a selection of 2-sep-2015 then this is my desired output:

CNTXT NM	IND	SUB CNTXT NM	ROWS	Date Uploaded
A	AA	AAA	12	2-Sep-15
		BBB	13	2-Sep-15
		CCC	14	4-Sep-15
B	BB	DDD	15	5-Sep-15
		EEE	16	6-Sep-15
		FFF	16	7-Sep-15

How will I achieve it? Any help is highly appreciable.

[jonathandienst]

How is TableB linked to TableA?

Please clarify - you say you want to include all CNTXT NM, but how do you know which one (rows 3 and 7 in table A)?

[#3]

I have created a autonumhash(Date Uploaded) key on Table A and B.

Table B is date dimension.

If there are multiple CNTXT NMs then, the one with max(Date Uploaded) has to be displayed. In this case, row 3 (4-Sep-2015).

So, my output is CNTXT NM belonging to the date selected and all other CNTXT NMs (doesn't belong to date selected) with their corresponding max(Date Uploaded)