Pie chart with calculated dimension (example file attached)

[tom2013]

Hello, 

I'm trying to make a pie chart, which illustrates how many percent of orders have one or more operations registered beyond expected finish date. 

For better understanding I have created the table below. Here if all operations are executed before expected finish date, the whole order will be marked as "ontime", otherwise "Delayed". 

The expression is rather simple: 

if(max( TOTAL <Order> Operation)<=Expected_Finish,'Ontime','Delayed')

The results in this table is exactly what I expected, and I wish to show the results in a pie chart (60% orders ontime, 40% orders delayed). But when I set the same expression as calculated dimensions in pie chart, it no longer works. 

Would this be possible to realize on front-end?

Any help will be appreciated. Thank you!

[kaushiknsolanki · Accepted solution]

Hi,

When you want to use the max functions in dimension then you should use the aggr function.

Like below.

Change Dimension expression to.

=Aggr(if(max( TOTAL <Order> Operation)<=Expected_Finish,'Ontime','Delayed'),Order,Expected_Finish,Operation)

And change expression to 

=Count(Aggr(if(max( TOTAL <Order> Operation)<=Expected_Finish,'Ontime','Delayed'),Order,Expected_Finish,Operation))

Find the attachment.

[Taoufiq_Zarra · Accepted solution]

@tom2013  are you looking for ?

attached the QVW file

[tom2013]

Hi Kaushik, 

Thank you for your reply!

Maybe I didn't state the problem clear enough - the desired result in the pie chart is how many "orders" are ontime. 

From the proposed solution you uploaded, the occurence of calculated dimension is counted, not the orders. If you reload the script you will see the ratio in the pie chart is 7:4, not 3:2. 

I tried to change expression to 

=Count(DISTINCT Order)

And it shows 3:2 again. Not sure if I did it in the right way though... 

[tom2013]

Hi Taoufiq,

Yes - and this solution is something I never expected! I actually thought about using aggr, but it's always nice to learn a new trick 🙂 

Thank you for helping!