
1. Re: Pareto graphs representation
Shinichiro Murakami Jul 26, 2016 5:49 AM (in response to Kateryna Chkalova)Hi Kateryna,
You can assign Column and Row ID for each Product group like this.
[Column ID]
case [Product group]
when "Cable Accessories" then 1
when "Connectors" then 2
when "High voltage" then 1
END
[Row ID]
case [Product group]
when "Cable Accessories" then 1
when "Connectors" then 1
when "High voltage" then 2
END
Thanks,
Shin

Pareto example_9.3.twbx 68.9 KB


2. Re: Pareto graphs representation
Simon Runc Jul 26, 2016 6:45 AM (in response to Kateryna Chkalova)hi Kateryna,
Although Shin's solution will work perfectly well...I had started on this question so may as well post my solution!!
My solution is a little more complicated, but does have the advantage of being fully dynamic where how ever many categories you have, and can flex how many columns it shows...
So first I create the following 4 formulas
//this one is used to number the categories (panels)
[index]
index()
//this is used to determine which row a Product Group will go on ([Columns] here is a parameter so we can select the number of columns
[RowVal]
[index]/[Columns]
//can't have part row assignment so rounds them
[Row]
if [RowVal]=int([RowVal]) then
[RowVal]
elseif
[RowVal]<0 then int([RowVal])
else
int([RowVal]+1)
end
//determines which column a Product Group will go in
[Column]
IIF(([index] % [Columns])=0,[Columns],[index] % [Columns])
I then bring (as discrete pills) [Row] onto the Row Shelf and [Column] onto the column shelf...and set the compute using to be Product Group (for both calculations). I also now remove the Product Group from the Columns shelf.
The final bit is that we want to Label them...so i've used a Dual Axis (on Zero) where I place the Product Group as a text mark. You can also use the Columns parameter to flex how many columns and it will work out where to put stuff!
This general technique is called 'Small Multiples'...which I use quite a lot. It's very useful when looking at differences between things, where the difference in pattern becomes quite apparent.
Hope this all makes sense, but please post back if not.

3. Re: Pareto graphs representation
Kateryna Chkalova Aug 5, 2016 5:42 AM (in response to Kateryna Chkalova)Hi!
Thank you very much. It is exactly what I need.
But I still have a problem. I just changed a bit database so that now the cumulated percents for product groups are not sequentially increasing and I see that the graphs are not separated by product groups now. Seems like only the last graph represents only one product group and others two show all the groups.
I attach the workbook, it would be great if you could take a look.
Thank you again!
Best regards,
Kateryna

Pareto example v2.twbx 89.7 KB


4. Re: Pareto graphs representation
Shinichiro Murakami Aug 5, 2016 6:28 PM (in response to Kateryna Chkalova)Kateryna
Unfortunately, it's quite difficult to analyze your workbook because the field of "Customer%" and "Customer% of" are given data, not calculated field.
Could you explain what you want to accomplish for that part.
This is way different from your initial request on original post.
Thanks,
Shin

5. Re: Pareto graphs representation
Simon Runc Aug 7, 2016 3:00 AM (in response to Kateryna Chkalova)hi Kateryna,
So this was driving me nuts!!...just couldn't work out a) what had changed in the data b) why that change had broken everything!! Eventually I found it...I'll only go briefly into the reason, as it's really the solution you need.
...so the previous version had an entry for every CustomerID/Product group. This meant that when the index(), used in the Row and Column table calculation, was run over Product Group CustomerID =1, say, had index 1 for Cable Acces, 2 for Connectors and 3 for High Voltage...so they all, nicely, went into the correct Row and Columns. However the v2 had gaps (so CustomerID = 1 only had sales in Cable Acces, and Connectors, and CustomerID = 2 only had sales in Connectors and High Voltage). This meant that (in the given example) CustomerID=1, index() [which determines the Row/Column] was 1 for Cable Acces, and 2 for Connectors, whereas CustomerID = 2 was 1 for Connectors and 2 for High Voltage)...suitably confused?...I certainly was!
So the solution requires us to use a slightly more advanced set up on the Table Calculations. So for both the Row and Columns we need to go into the Advanced Set up...Click on Row/Column, Edit Table Calculation > Select Advanced (under the Compute Using dropdown), and set up as per the below (where the order in the addressing needs to be Product group 1 then Customer ID1
Click OK and the set the 'At the Level' to Product group1
In the attached (on the OP and SR tabs) I've set up work sheet actions so if you click a bar it will highlight that Product group 1...so you can see the SR version has all the bars in the right chart, and the OP one doesn't
Hope this makes sense, but if not that the new solution does the trick.
I've actually learned quite a bit myself doing this one...this is why I love the Community!!

6. Re: Pareto graphs representation
Kateryna Chkalova Aug 8, 2016 1:57 AM (in response to Kateryna Chkalova)Hi,
oh, that works perfectly!! Thank you very very very much!!!
And I am sorry for complex database and unclear explanation of the problem in my question :(

7. Re: Pareto graphs representation
Simon Runc Aug 8, 2016 1:58 AM (in response to Kateryna Chkalova)no problem...once the data had changed to something 'more real world' it was a more complicated problem that I initially thought. Got there in the end, which is the important thing!!