6 Replies Latest reply on Dec 6, 2018 3:40 PM by Yuriy Fal

Divide a measure by itself with varying only one dimension/column

The source data will typically contain 6-ish factors, and 1 data columns (Rate) that I want to apply the division to. The number of factors may change, and the levels within each factor may also change, e.g. Age will sometimes be quinquennial, sometimes 1-incremental, and starting and ending ages may also be different.

For each factor, I want to create a sheet that will divide the (Rate) field by itself with the factor of interest offset by one.

For example, in the Age sheet, the data in the grid should represent Age_(x+1)/Age_x, given that all the other factors are the same.  In essence, I am attempting to create a "heat map" that lets me quickly see the trend of the Age_(x+1)/Age_x movements. In here the (x+1) is technically referring the "next" age in the data, rather than actual x+1 age value.

In this workbook, the workaround current is

'IF(LOOKUP(AVG([Age]),1)>AVG([Age])) THEN LOOKUP(AVG([Rate]), 1)/AVG([Rate]) ELSE NULL END'

However, it is not ideal since if you look at the Band sheet, it fails to produce what I want. Essentially, each factor, or dimension, should be in an order that I specify (e.g. smallest to largest, A to Z), and I want to compare the two adjacent levels of a dimension.

• 1. Re: Divide a measure by itself with varying only one dimension/column

Good morning Harley -

Not certain I understand the goal but the formulas that you created are looking at individual records in the data set and identifying the point at which one of the dimensions changes - eg age goes from 39   to  35 - It then looks at the current record avg (that is the average of that single record not all the records in the "30 group" and compares it to the first record in the next group "the 35 group"

If you coal is to compare averages in the entire age subgroups you will need to start by creating the subgroup averages using LOD's - (the LOD will create the average for the subgroup and return it as a single value that is NOT an aggregate in itself)  then use the LOD expressions in you conditional statement that looks for the divisions between groups

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: Divide a measure by itself with varying only one dimension/column

Thanks Jim.

The reason that I used AVG is because LOOKUP can't simply take [Rate] as is, even though there is only 1 unique value of [Rate] with the way I set-up the grids in the sheets.

For example, in the Age sheet, the cell of Age 30 and Term 12 should be a formula of
([Rate] of the next age in ascending order)/([Rate] of the current age 30), with all other variables/factors being the same between the numerator and denominator.

• 4. Re: Divide a measure by itself with varying only one dimension/column

Hi Harvey,

With your existing view setup and calculations,

you may want to take care of the proper Addressing

For example, your Band calc should be

Compute using --> SA Band (only this Dim).

This would create the table calculation windows

with only the [SA Band] values changing (in the sort order) --

and every other Dimensions values to remain the same.

Hope it could help a bit.

Yours,

Yuri

• 5. Re: Divide a measure by itself with varying only one dimension/column

So I think I got it to work. But issue now is that, when I try to apply color to get a heat map visualization, it colors all the blank as the lowest level.

The formula I have for Age measure is now,

IIF(ZN(SUM([Rate])) / LOOKUP(ZN(SUM([Rate])), -1)=0, NULL, ZN(SUM([Rate])) / LOOKUP(ZN(SUM([Rate])), -1)) I have also attached the Workbook, it's sheet "Age1".

• 6. Re: Divide a measure by itself with varying only one dimension/column

Hi Harley,

You may want to apply the [Age1Rate]

as a Table Calculation Filter,

and select Non-Null Values.

This effectively hides the Marks

where [Age1Rate] is Null.