-
1. Re: Show all columns (months) based on filter
suman kumar May 14, 2018 4:32 AM (in response to Charles Prodon)Here I have attached screen shot. do you want exactly the same filter here..?
Please let me know if you want the same.
Anyway Here I have attached workbook as well. if you'll filter quarter wise then you'll be able to see what exactly you are looking for.
Thanks
Suman
Please mark helpful if it works for you.
-
Test month_v10.3.twbx 12.3 KB
-
-
2. Re: Show all columns (months) based on filter
Charles Prodon May 14, 2018 4:49 AM (in response to suman kumar)Thank you Suman for the quick reply, but it doesn't seem to be what I am looking for.
My focus is not so much the filter as such, ideally I would prefer it to remain dynamic, i.e. that the user can filter on specific months, quarters, etc.
What I need is that the table shows all months within that filter, including the month that have no data.
In the workbook you attached, if I select Q1 only, then the table displays only Jan and Feb. I would like March to be displayed as well as blank since there is no data.
-
3. Re: Show all columns (months) based on filter
Zhouyi ZhangMay 14, 2018 4:54 AM (in response to Charles Prodon)
Hi, Charles
Please find my solution attached by joining your data to a calendar table as shown below
Hope this helps
ZZ
-
Test month_v10.2.twbx 19.6 KB
-
-
4. Re: Show all columns (months) based on filter
Charles Prodon May 14, 2018 12:28 PM (in response to Zhouyi Zhang)Hello Zhouyi - thanks for taking the time to help.
I understand the work around to 'complete' my database with all missing month and that works perfectly and easily with the example I enclosed.
Unfortunately, I might have over simplified the example compared to my real data. Indeed, my data is a bit more complex in the sense that I have many more dimensions. To continue with the same example, let' say that my database includes further dimensions such as Market, Region, Key Account, Products. And I want the users to be able to filter on any of these dimensions and see the monthly data including the months without data. I have enclosed the updated workbook, but please consider that this is still a simplified version of the reality as I have thousands of products.
In order to use your workaround (if am I not wrong) I would have to create a calendar file that includes all possible combinations of Market, Region, Key Account, Products and Months to ensure that I capture all possible options. Plus, I would have to maintain that database for every new Product or change in Key Account.
Again I recon that your solution can work, but I am afraid it is not manageable with my real data.
-
Test month.twbx 14.1 KB
-
-
5. Re: Show all columns (months) based on filter
Zhouyi ZhangMay 14, 2018 11:35 PM (in response to Charles Prodon)
Hi, Charles
You don't have to create the combination of dimension with calendar in this case. I did the same join with your new workbook and below is the result by play around some of the dimension filter as well.
Workbook attached for your reference.
Hope this helps
ZZ
-
Test month_v10.5.twbx 24.3 KB
-
-
6. Re: Show all columns (months) based on filter
Charles Prodon May 15, 2018 1:10 PM (in response to Zhouyi Zhang)Hi Zhouyi,
I cannot open your last workbook as I only have v10.3.
Anyway, I have replicated in the enclosed workbook what I think is a limitation of your proposal:
In this example I have sales covering all days of February on various products with exception of product BB. The joint with Calendar will not create a line for Feb for BB and therefore a filter on product BB (and Null) will show all months but February.
Please let me know if I am missing something?
One additional downside of your proposal is that the user always has to filter on his selection + 'Null' which is not very intuitive.
Thanks again,
Charles
-
Test month.twbx 24.3 KB
-
-
7. Re: Show all columns (months) based on filter
Zhouyi ZhangMay 15, 2018 5:57 PM (in response to Charles Prodon)
Hi, Charles
I don't think user select 'Null' with other values together for filter purpose. please refer to my comments and steps I did to show Feb when BB is selected.
Workbook attached for your reference.
Hope this make sense.
ZZ
-
Test month (1)_v10.3.twbx 22.9 KB
-
-
8. Re: Show all columns (months) based on filter
Charles Prodon May 17, 2018 4:04 AM (in response to Zhouyi Zhang)Dear Zhouyi,
I am grateful, the "add to context" solves my issue.
Thanks a lot for your help and taking the time to explain the steps.
Charles
-
9. Re: Show all columns (months) based on filter
Zhouyi ZhangMay 17, 2018 4:40 AM (in response to Charles Prodon)
you are welcome, and I am glad to help
ZZ