-
1. Re: Level of Detail Calculation with a Running Sum
Dave Rawlings Feb 24, 2016 6:11 AM (in response to Dave Rawlings)Anyone any ideas?
-
2. Re: Level of Detail Calculation with a Running Sum
pooja.gandhi Feb 24, 2016 6:15 AM (in response to Dave Rawlings)Hey Dave!
I would suggest uploading atleast a few sample rows of your data. Probably if there are other fields, the solution could hinder based on whats on the view. I am not sure running_sum would work because the values would change if the measure value is sorted vs. not sorted. Customers making up 80% of the total revenue would change I would assume.
Pooja.
-
3. Re: Level of Detail Calculation with a Running Sum
Dave Rawlings Feb 24, 2016 6:25 AM (in response to pooja.gandhi)Unfortunately uploading anything just isn't allowed.
Running sum works, as long as the clients are shown on screen, as soon as I try to create a summary table of the data the top 80% recalculates based on what is now shown on screen. I'd like to stop that happening.
-
4. Re: Level of Detail Calculation with a Running Sum
Shinichiro MurakamiFeb 24, 2016 6:33 AM (in response to Dave Rawlings)
I'm not sure where you have trouble regarding LOD?
Here is a sample.
[Sales Share %]
{fixed [Client] :sum([Sales])}/{fixed : sum([Sales])}
Top 80% In/Out
if RUNNING_SUM(sum([Sales Share %]))<=0.8 then "IN" else "OUT" END
I'm not sure about your comment of LOD part?
Thanks,
Shin
9.0 attached
-
Top80%_9.0.twbx 52.2 KB
-
-
5. Re: Level of Detail Calculation with a Running Sum
Dave Rawlings Feb 24, 2016 6:43 AM (in response to Shinichiro Murakami)Thanks I'll take a look.
In that data would you be able to produce a table that was in this format:
Top 80 Count of Clients Sum of Sales
In X £X
Out X £X
-
6. Re: Level of Detail Calculation with a Running Sum
Shinichiro MurakamiFeb 24, 2016 7:02 AM (in response to Dave Rawlings)
That's a good question, let me try.
Thanks,
Shin
-
7. Re: Level of Detail Calculation with a Running Sum
Dave Rawlings Feb 24, 2016 7:17 AM (in response to Shinichiro Murakami)Thanks. That's kind of where I'd like to get to and can't quite get there
-
8. Re: Level of Detail Calculation with a Running Sum
Shinichiro MurakamiFeb 24, 2016 8:47 AM (in response to Dave Rawlings)
OK, finally done.
You can visit here Vizible Difference: Binning Aggregates for Histogram and Aggregate Distributions
But quite a bit tricky.
Need to use window_sum, window_count and use "hide header" technique.
The point here is we cannot remove "Client" from the Marks because this is the key to determine the Top 80%, IN/OUT.
So we need to show a certain client's line as a representative of each category.
Then flag that client and hide others.
These are story line of the whole procedure.
Put [Client] in Marks and sort
Create calculated filed and set Set Default as below.
[window_sales]
case [Top 80% In/Out]
when "IN" then window_sum(if [Top 80% In/Out]="IN" then sum([Sales]) end)
when "OUT" then window_sum(if [Top 80% In/Out]="OUT" then sum([Sales]) end)
END
[Window_count]
case [Top 80% In/Out]
when "IN" then window_count(if [Top 80% In/Out]="IN" then count([Client]) end)
when "OUT" then window_count(if [Top 80% In/Out]="OUT" then count([Client]) end)
END
Put two fields into Measure Values and edit table calculation
One more calculated field
[Client Flag]
IF First()=0 OR [Top 80% In/Out]!=lookup([Top 80% In/Out],-1)
THEN [Window_Sales]
ELSE NULL
END
Then hide Null from Client Flag. (Not exclude, just hide)
Then hide headers.
Enjoy !!!!
Thank you ,
Shin
9.0 attached.
-
Top80%_2_9.0.twbx 81.8 KB
-
-
9. Re: Level of Detail Calculation with a Running Sum
Dave Rawlings Feb 25, 2016 1:27 AM (in response to Shinichiro Murakami)Thanks, that looks great