5 Replies Latest reply on Jan 8, 2019 4:39 AM by Jeanne Sun

# Calculate difference by different dimension

Hi guys,

I got some data that looks like the "Raw data" below. What I want to calculate (see "Expected output 1") is for a particular "Fund" (e.g. xyz or lmn) the difference of the "Ongoing cost" between "with distribution cost" and "Clean charge", ignoring "no mgt and distri cost" (i.e. treated then as null).

Raw data:

Fund Ongoing costShare class fee basis
xyz0.78Clean charge
xyz1.43with distribution cost
lmn 0.69Clean charge
lmn 1.27with distribution cost
lmn 0.08no mgt and distri cost

Expected output 1:

Fund Ongoing costShare class fee basis
Distribution cost
xyz0.78Clean charge0.66
xyz1.43with distribution cost 0.66
lmn 0.69Clean charge 0.58
lmn 1.27with distribution cost 0.58
lmn 0.08no mgt and distri cost null

• ###### 1. Re: Calculate difference by different dimension

Hello Jeanne,

Thanks!
Sankar M

• ###### 2. Re: Calculate difference by different dimension

Hello Jeanne!

I've attached an example I was able to throw together; you may be able to do this in fewer steps than me.

I started with an LOD expression that pulls the value by Fund:

Dimension Difference

{fixed [Fund]:sum(iif([Share class fee basis] = "with distribution cost",[Ongoing cost],0))}

-

{fixed [Fund]:sum(iif([Share class fee basis] = "Clean charge",[Ongoing cost],0))}

NOTE: This could generate some unusual values these are not present in a fund, or if they are listed more than once. Similarly, you could get a negative number if your Clean charge is > distribution charge, but that's easy to fix!

Next I dropped our dimension [Share class fee basis] into a SET and selected ONLY the two values we're interested in:

"Why on earth use a set for this?!?"

Well, the truth of the matter is you don't have to. But sets have some really cool properties that can make programming & debugging a breeze.

• They're fast to set up! One click and you're ready to filter!
• You don't have to worry about spelling! The sets take just what's in your data, so you when writing code you don't need to worry about if you spelled something correctly or not. Bazinga!
• They make checking VERY FAST! Used in calcs or filters, sets work fast to immediately tell you if a value is IN or OUT... you don't need to work your fingers to the bone creating long lists of nested IF statements.

After our set is made, we can combine the two

This simply checks if the value is one of the two we want. If so, it displays the calculated difference. Otherwise, it displays nothing. NOTE: If you want it to actually display the word NULL rather than a blank, you'll also need to convert your Dimension Difference formula into a string. I went for a short cut.

The last thing I did was playing around with the rows to sort the data as I saw in your picture. I included the [Diff or Null] calc, which I hid, to sort null values to the bottom.

I hope this helps!

--Michael

• ###### 4. Re: Calculate difference by different dimension

Hi Jeanne,

Look at the top right corner of the view. You will find the option to

• ###### 5. Re: Calculate difference by different dimension

Thanks Michael.

The Dimension Difference formula is spot on. I did some changes to make it return null.

{FIXED [Fund Standard Name]:AVG(IIF([Share Class Fee Basis]="Mgmt + Dist Fee",[Ongoing Cost],NULL))}

-

{FIXED [Fund Standard Name]:AVG(IIF([Share Class Fee Basis]="Clean (Mgmt Fee only)",[Ongoing Cost],NULL))}