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 basisxyz0.78Clean chargexyz1.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 basisDistribution cost
xyz0.78Clean charge0.66xyz1.43with distribution cost 0.66lmn 0.69Clean charge 0.58lmn 1.27with distribution cost 0.58lmn 0.08no mgt and distri cost null
Many thanks in advance!
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
