8 Replies Latest reply on May 9, 2019 12:34 PM by Jonathan Drummey

# LOD Calc and Table Calc - Any workarounds to get at the needed results?

Hello everyone,

In the following workbook, I have mocked up a work issue that I am having.

I have one worksheet which shows the recent market share for a chosen product (user filter) by State. This market share is calculated by taking the sum of quantity for the recent four months for the chosen sub-category and dividing it by the sum of quantity for the recent four months for the whole state (all sub categories). This sheet works.

Then, I have a second worksheet which attempts to show the market share over time. THIS IS WHERE THE ISSUE IS. We'll come back to this. (Top chart (Sum(Quantity) is not necessary, it's just there for your reference)

Then, I have a dashboard with the worksheets above. The idea is that the user can select a country and then see the trend of the market share over time (second worksheet).

The issue is that I cannot get a solution for the second worksheet. How can I show the market share (sum of the four values of subcategory A / sum of the four values of all subcategories ({Fixed State: Sum(Quantity)}as a trend over time.

The difficulty is in mixing the action (dashboard selection, LOD calc (fix the denominator of market share to be all subcategories regardless of filter), and table calc (sum the current value and previous 3 of filtered subcategory to get numerator). I cannot figure out any way around this.

Instead of beating my head against the wall for the next 3 days, I figured there is probably someone out there in the community who is a lot smarter than me.

• ###### 1. Re: LOD Calc and Table Calc - Any workarounds to get at the needed results?

Hi

Not certain I am tracking with you but is this the expected result

to get here I removed the rank function and replaced it with a sort by market share

that gets you out of the table calc on the first sheet - yo can not pass a table calc from one sheet to another - they are dependent on the sheets underlying table which are unique to the worksheet

Jim

If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

• ###### 2. Re: LOD Calc and Table Calc - Any workarounds to get at the needed results?

Hello Jim Dehner,

This is not quite what I am going for. The second sheet is currently the Sum of the current value and previous three months for the filtered subcategory. I need to take that number and divide it by the current value and previous three months for all subcategories to get that subcategories market share of total quantity for the state.

Window_Sum(Sum(Quantity),-3,0)

{FIXED State: Window_Sum(Sum(Quantity),-3,0)} (all subcategories because the FIXED overrides the dimension filter)

Only the above calc is not possible because you cannot have a table calc in an LOD expression, but this is the idea that I am trying to get at.

Best,Mikhail

• ###### 3. Re: LOD Calc and Table Calc - Any workarounds to get at the needed results?

Jonathan Drummey, any tricks up your sleeve?

• ###### 4. Re: LOD Calc and Table Calc - Any workarounds to get at the needed results?

Hi Mikhail,

I noticed you're using v10.5 for this, is there any chance of upgrading to v2018.3 or later to use Set Actions instead? Alternatively, can the filter on Sub-Category use a parameter (which would allow single select) instead of the present multi-select filter?

I can get a solution, but it's more complicated than I'd like due to the sparseness of the data.

Jonathan

• ###### 5. Re: LOD Calc and Table Calc - Any workarounds to get at the needed results?

Thanks, Jonathan Drummey, for taking a look!

I could probably get away with switching the filter to a parameter. However, the company cannot yet upgrade from 10.5 to 2018.3.

Could you walk me through the possibility of how to set it up if I could do the upgrade?

• ###### 6. Re: LOD Calc and Table Calc - Any workarounds to get at the needed results?

Hi, here are two versions. The 10.5 version uses a parameter, the 2018.3 version uses a set action. There are a couple of additional possibilities using padded or self-unioned data, I skipped those for the added complexity.

Jonathan

• ###### 7. Re: LOD Calc and Table Calc - Any workarounds to get at the needed results?

Thank you, Jonathan Drummey! These were both great options and we just got a notice that we will be upgrading from 10.5 to 2018.3 so both ended up being very relevant! Thank you!

• ###### 8. Re: LOD Calc and Table Calc - Any workarounds to get at the needed results?

You're welcome!