I have prepared a pivot table in quicksight dashboard. There are 9 rows in the pivot table which are hierarchy based. level 1 , level 2, level 3 till level 9. So product in a hierarchy may have further hierarchy or it may not. And on top of it some calculated fields are added in values so that those values can be checked for each level of hierarchy in pivot drill down. The issue here is, for the hierarchies where no further products are available, the rows keep on showing “empty” in further drill downs till level 9. e.g. any product which is having products till hierarchy level 5, shows empty rows for more 4 drill downs with the same values. How to restrict drill downs to the level the data exists or remove the “empty” rows ?
I have tried removing blanks via filter by removing blanks but it removes the product line altogether.
seems it was caused by the logic in calculated field. Can show the script of the calculated field? and a screenshot of the dashboard?
Hi, @royyung, Thank you for replying. The value fields are straightforward calculations for net revenue, infra cost etc. for example “ifelse({category}=‘Net Revenue’, {actual_amount}, 0)” that gives me net revenue. The issue I am facing is in the row fields being empty.
I am trying to put it once again : Let’s assume Product- A sits on first row of pivot table which level 1 hierarchy for me, Now it has further levels of sub-products which sit on level 2 hierarchy i.e. second row of my pivot table. Now, if i click on the (+) icon beside product- A, it will drill it down and show sub-products of A and show values of those sub-products. In case some products sitting a level 1, for example Product-G, have no further sub-products, then in that case it should not show (+) for those products but it still shows the drill option and shows “empty” as row till whatever the number of rows I have entered in the pivot table. I want to restrict the drill down before empty part or know in which way I can hide/ remove those empty rows so that it will show drill down till the level where data exists. If I go the row filter for level 2 and remove the blanks, then product-G is removed altogether from the pivot table.
I have tried and cannot see the issue you mentioned
can try to create a sample in Arena according to this page?
Hi @sohelpm,
It’s been awhile since we last heard from you, did you have any additional questions regarding your initial post?
If we do not hear back within the next 3 business days, I’ll go ahead and close out this topic.
Thank you!
Hi @sohelpm,
Since we haven’t heard back, I’ll close out this topic. However, if you have any additional questions, feel free to create a new topic in the community and link this discussion for relevant information if needed.
Thank you!