Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have a pivot table showing two dimensions: Organizations in rows, MonthYear in columns, w/ count of meetings as the expression.
When I select the MonthYear(s) I want to view, the table looks perfect (see below img A). The months with no values remain because other organizations have values in that column. When I select a single organization (img B) the null/missing months disappear. What do I need to do for these to stay? Create a query that populates the missing data?
The one thing I have tried is to enable "Show all values" on the MonthYear but then I get every MonthYear from my Master Calendar (img C). Can I somehow hide the MonthYear(s) that are not selected by the user?
A.
B.
C.
Hm, that's not an easy one given the fact that both fields you are selecting in are dimensions of your table.
It should work if you add the missing combinations of MD_Business_Units and MD_MonthYear to the fact table (without adding values for the other fact fields:
TMP:
LOAD DISTINCT Date(MD_MonthYear) as MD_Created_Date
RESIDENT MD_MasterCalendar;
JOIN
LOAD MD_Business_Units
Resident Meeting_Details;
Concatenate (Meeting_Details)
LOAD * Resident TMP;
DROP Table TMP;
Have you tried unchecking 'Suppress When Zero' on the presentation tab?
I currently only have "Populate Missing Cells" checked in the presentation tab.
Updated the original post with one thing I did try and the closest I've been to what I actually want.
Would it be possible to share a sample to play around with?
See attached.
Maybe like
=If(Count(MD_ID)+sum({<MD_Business_Units>}0)<>0,Count(MD_ID))
Looks like a start but selecting a single organization in that table shows all Organizations and not just the selected
Hm, that's not an easy one given the fact that both fields you are selecting in are dimensions of your table.
It should work if you add the missing combinations of MD_Business_Units and MD_MonthYear to the fact table (without adding values for the other fact fields:
TMP:
LOAD DISTINCT Date(MD_MonthYear) as MD_Created_Date
RESIDENT MD_MasterCalendar;
JOIN
LOAD MD_Business_Units
Resident Meeting_Details;
Concatenate (Meeting_Details)
LOAD * Resident TMP;
DROP Table TMP;