16 Replies Latest reply: Jul 30, 2018 3:32 AM by sagar jagga

# Bar chart creation using particular fields from dimensions

Hi All,

I am facing a challenge to create a bar chart using 2 dimensions for below data set.

 Manager Supervisior PAT Roll No Anindya ABC 1 Anindya ABC 11 Anindya ABC 22 Anindya ABC 33 Anindya DEF 10 Anindya DEF 10 Mehtab ABC 33 Mehtab FED 44 Sagar ABC 21 Sagar FED 33

I need to create bar chart only for Anindya and sagar who are under manager and for supervisiors ABC,FED and bar chart should be 'grouped bar chart' and it should show share of count of 'PAT Roll No' under supervisior and manager dimension in respective bars. for example count of PAT roll no in Anindya in ABC and FED is 4(as there is no FED for anindya) so ABC bar chart will show 100%(4/4) and for FED it will be 0%

Then for sagar count of pat roll no under ABC and FED is 2(1+1) so 50% for ABC bar(1/2) and 50% for FED bar(1/2)

Thanks,

• ###### Re: Bar chart creation using particular fields from dimensions

Try this

Count({<Supervisior = {'ABC', 'FED'}, Manager = {'Anindya', 'Sagar'}>}[PAT Roll No])/Count(TOTAL <Manager> {<Supervisior = {'ABC', 'FED'}, Manager = {'Anindya', 'Sagar'}>}[PAT Roll No])

• ###### Re: Bar chart creation using particular fields from dimensions

Hi Sunnny,

Thanks a lot. but i have one problem, PAT Roll no . can be duplicate in particular Manager and supervisior combitions. so i need to count only distinct PAT roll no under particular Manager -> Supervisior.

although a same pat roll no can come under different manager supervisior combination but that is not the concern.

I am trying to use distinct in your set analysis but getting an error.

Count(distinct {<Supervisior = {'ABC', 'FED'}, Manager = {'Anindya', 'Sagar'}>}[PAT Roll No])/Count(TOTAL distinct  <Manager> {<Supervisior = {'ABC', 'FED'}, Manager = {'Anindya', 'Sagar'}>}[PAT Roll No])

• ###### Re: Bar chart creation using particular fields from dimensions

No error. it is giving me wrong value

• ###### Re: Bar chart creation using particular fields from dimensions

May be try this... moved <Manager> before DISTINCT

Count(DISTINCT {<Supervisior = {'ABC', 'FED'}, Manager = {'Anindya', 'Sagar'}>}[PAT Roll No])/Count(TOTAL <Manager> DISTINCT {<Supervisior = {'ABC', 'FED'}, Manager = {'Anindya', 'Sagar'}>}[PAT Roll No])

• ###### Re: Bar chart creation using particular fields from dimensions

Count(TOTAL <Manager> DISTINCT {<Supervisior = {'ABC', 'FED'}, Manager = {'Anindya', 'Sagar'}>}[PAT Roll No])

Above expression is finding total for distinct PAT roll No. means to say, while calculating Total of count it is at first applying distinct on PAT Roll No and returning the total.

 Manager Supervisior PAT Roll No Anindya ABC 1 Anindya ABC 11 Anindya ABC 22 Anindya ABC 1 Anindya ABC 22 Anindya ABC 33 Anindya DEF 10 Anindya DEF 10 Mehtab ABC 33 Mehtab FED 44 Sagar ABC 22 Sagar FED 33

it is considering 22(which is present under different Manager and supervisior as well) once

• ###### Re: Bar chart creation using particular fields from dimensions

I am not sure I follow? Is there a question for me here?

• ###### Re: Bar chart creation using particular fields from dimensions

Hi Sunny,

Me and anindya work in same team. Inadvertently i replied you from his ID

• ###### Re: Bar chart creation using particular fields from dimensions

That is okay, I figured that part out... but what I am not sure about is the output you are expecting to get? Based on the sample provided... what is the numerical output you are looking to get?

• ###### Re: Bar chart creation using particular fields from dimensions
 Manager Supervisior PAT Roll No Anindya ABC 1 Anindya ABC 11 Anindya ABC 22 Anindya ABC 1 Anindya ABC 22 Anindya ABC 33 Anindya FED 1 Anindya DEF 10 Anindya DEF 10 Mehtab ABC 33 Mehtab FED 44 Sagar ABC 22 Sagar FED 33

 Manager Supervisior Count Distinct PAT Roll No Percentage of Share Anindya ABC 4 4/5=80% Anindya DEF 1 1/4=20% Sagar ABC 1 1/2=50% Sagar FED 1 1/2=50%

I am finding out the share of ABC,DEF in ABC and DEF for these two managers. but by using previous denominator expression Total for Anindya -> ABC,DEF is coming 4(because it is counting PAT roll no1 as single unit but it should have calculate PAT roll no 1 from FED as well).

• ###### Re: Bar chart creation using particular fields from dimensions
 Manager Supervisior Count Distinct PAT Roll No Percentage of Share Anindya ABC 4 4/5=80% Anindya DEF 1 1/5=20% Sagar ABC 1 1/2=50% Sagar FED 1 1/2=50%

Final chart or table

• ###### Re: Bar chart creation using particular fields from dimensions

Sorry in previous replies i baffled in FED and DEF

 Manager Supervisior PAT Roll No Anindya ABC 1 Anindya ABC 11 Anindya ABC 22 Anindya ABC 1 Anindya ABC 22 Anindya ABC 33 Anindya FED 1 Anindya DEF 10 Anindya DEF 10 Mehtab ABC 33 Mehtab FED 44 Sagar ABC 22 Sagar FED 33

 Manager Supervisior Count Distinct PAT Roll No Percentage of Share Anindya ABC 4 4/5=80% Anindya FED 1 1/4=20% Sagar ABC 1 1/2=50% Sagar FED 1 1/2=50%

I am finding out the share of ABC,DEF in ABC + FEDfor these two managers. but by using previous denominator expression Total for Anindya -> ABC,DEF is coming 4(because it is counting PAT roll no1 as single unit but it should have calculate PAT roll no 1 from FED as well).

• ###### Re: Bar chart creation using particular fields from dimensions

How is 1/4 = 20%? is it 1/5 or are you looking to get 25%?

• ###### Re: Bar chart creation using particular fields from dimensions

1/5 = 20%

 Manager Supervisior Count Distinct PAT Roll No Percentage of Share Anindya ABC 4 4/5=80% Anindya FED 1 1/5=20% Sagar ABC 1 1/2=50% Sagar FED 1 1/2=50%

I am finding out the share of ABC,FED in ABC + FED for these two managers. but by using previous denominator expression Total for Anindya -> ABC,FED is coming 4(because it is counting PAT roll no1 as single unit but it should have calculate PAT roll no 1 from FED as well).

• ###### Re: Bar chart creation using particular fields from dimensions

Try this

Count(DISTINCT {<Supervisior = {'ABC', 'FED'}, Manager = {'Anindya', 'Sagar'}>}[PAT Roll No])/

Sum(TOTAL <Manager> Aggr(Count(DISTINCT {<Supervisior = {'ABC', 'FED'}, Manager = {'Anindya', 'Sagar'}>}[PAT Roll No]), Manager, Supervisior))

• ###### Re: Bar chart creation using particular fields from dimensions

Hi Sunny,

You are genius and so much helpful.

Thanks for the help