Hi Manideep, your method above works. Thanks a lot~
Actually my real situation is more difficult than the one above. I've deleted the data of California for Sep - Dec of 2014. Now I have to show a blank column for 3 metrics (i.e. profit, quantity and sales) in a table for 2014 Q4 of California. This is closer to my real task. Is there any way to do this? Pls. see the workbook attached.
Hello Reg - You need to create all the measures and plot it the way I have shown earlier. But honestly speaking, this method is really cumbersome and non-sustainable.
1 of 1 people found this helpful
Another approach I would recommend is creating a Date dimension table. (Either using spreadsheet or at database level).
If you do it at the database level, you can use LEFT OUTER JOIN.
If you do it with spreadsheet, creating a link and use Date sheet as primary data source
Attached is the workbook. Hope it helps
I think I figured out an easy way to do this. If you use a calculated field to exclude the data, then it'll leave the columns but return Null values. Check out the calculation I used for Q2 2014. If you change the date to the beginning date of your quarter you want to null, it should work the same way. Let me know if this makes sense.
Hi Tien, thanks for your info. Your approach works in this case. But since it involves creating another primary data source, it may not be applicable to my real workbook as the data relationship will be changed.
Hi Derek, thanks for your help. Your solution works if the missing quarter is Q2. But what about Q4? It seems that your calculated field above can't show a blank column if the data are missing in Q4.
I find that the function "Show empty columns" under Table Layout of Analysis can achieve my goal. But since my real workbook is having a secondary data source, this function is disabled. Too bad~
It should work exactly the same, Reg. If you change the date to "10/1/14", it'll work for Q4. A Caveat, though--it sounds like you already have deleted the data in the data source for Q4, this method may need data fed into it in order to return a null value. Basically, if you feed the original data source (the one that still has Q4 data) and change the date as above, then this method should work in the same way.
Additionally, if the original Q4 data can't be retrieved, you can add in a "fake" row of Q4 data (it doesn't matter what you put, because the calculated field will filter it out). As long as the date registers within Q4, the calculated field should bring it in, and because of that, a blank column.