6 Replies Latest reply on Aug 11, 2017 3:16 PM by Shinichiro Murakami

# Column level calculation

Hi People,

Want your help in solving the below problem.

My data set is something like this:

 GPID Week No Level Budget 1 30 High 1784 2 30 High 3871 3 30 Low 3477 4 30 Medium 1733 1 31 Low 3700 2 31 Low 3780 3 31 Low 3609 4 31 Medium 2309

Need to create a report to display :

How many GPIDs have moved from level High to Low in two different week and probably if i could highlight the accounts too.

I'm not sure how we implement column level calculation.

And Thank you all in advance.

• ###### 1. Re: Column level calculation

can you attach sample data. I can't copy this.

• ###### 2. Re: Column level calculation

Deepak

Find attached sample data set excel.

• ###### 3. Re: Column level calculation
1. Drag GPID to Rows
2. Drag Week No To Cols.
3. Redefine your Level as Numeric - 1,2,3
4. Drag that to your marks /abc.
5. Create a table calculation Type "Difference from", Compute Using "Table(across)", Relative to "Previous" - wherever your result is 2, you have a high-to-low.

You won't be able to filter by this though, but it's a start.

• ###### 4. Re: Column level calculation

Hey Ray,

Thank you looking into the problem.

The above solution is valid if a have a smaller data set. My actual data set is a heavy one and looking manually would be tough one.

I even tried with Grand Totals and then filter. But it didn't work too.

We need to look for a another highlighting solution.

Thanks

• ###### 5. Re: Column level calculation

Hi Vaibhav,

Check Screenshot and attached. It is a bit complex. So first you need to convert your level to numeric values by this formula:

Then I wrote few LODs to get what you Need. So finally you get this number for the number of GPIDs changed between 2 weeks. All the LODs are in attached. Please follow them there.

Hope it Helps!!!

Thanks

Deepak

• ###### 6. Re: Column level calculation

Hi Vaibhav

Couple of layers' table calculation are needed.

Specify the first condition by ID

Specify 2nd condition by ID

Count "ID" which met both conditions

To Ignore ID as summary table, add window_sum

To filter the view (hide duplicated ID), add idex filter.

Thanks,

Shin << Ambassador Spotlight Here ! >>