8 Replies Latest reply on Oct 6, 2016 7:07 AM by Rajeev Pandey

# Headache for me - (sum of records based on combination of two columns)Dont know How to use this Calculation

Hi.

I have two columns Federal indicator and USDC /PD . I want to take the count of all Federal  based on the below condition.

When Federal Indicator is "Federal"  then count the Number of rows . When  Federal indicator = Federal and USDC column is not equal to USDC or USDC Onsite  then count the number of Rows (No of Records) . I could not able to implement  both the condition in the same bar graph. This is only working when I am commenting one condition and showing others.

I am confused how to implement the same . Please help me in getting this Issue Resolved

Attaching the sample data set which contains three columns (as I tweaked the Column count)

My actual Calculation :

IF [Req. User Group]='US GLS India' THEN 'USI'

ELSEIF ([Usdc/Pd]='USDC' or [Usdc/Pd]='USDC-onsite') THEN 'USDC'

ELSEIF ([Federal Indicator]='Federal' and ([Usdc/Pd]<> 'USDC' or [Usdc/Pd]<>'USDC-onsite')) THEN 'FEDERAL WITHOUT USDC'

ELSEIF ([Federal Duplicate]='Federal' and([USDC/PD Duplicate]='USDC' or [USDC/PD Duplicate]='USDC-onsite')) THEN 'Federal'

ELSEIF [Federal Indicator]='Commercial' THEN 'US COMMERCIAL'

END

• ###### 1. Re: Headache for me - (sum of records based on combination of two columns)Dont know How to use this Calculation

Rajeev,

Your statement and excel data does not match and quite difficult to understand what is the issue?

Thanks,

Shin

• ###### 2. Re: Headache for me - (sum of records based on combination of two columns)Dont know How to use this Calculation

Hi Shinchirio,

I just remove the column while uploading the data which you highlighted below. Please donot consider the Federal duplicate column. I have just added the First column

• ###### 3. Re: Headache for me - (sum of records based on combination of two columns)Dont know How to use this Calculation

Still not clear. I don't see any "Federal and USDC" in Federal indicator" ?

What's the issue again?

When  Federal indicator = Federal and USDC column is not equal to USDC or USDC Onsite  then count the number of Rows (No of Records) .

• ###### 4. Re: Headache for me - (sum of records based on combination of two columns)Dont know How to use this Calculation

HI Shin,

So what I want is Count the no of rows when Federal indicator is equal to Federal . NO need to see any other Values . In the very same calculated Field , I want(  if Federal Indicator = Federal and USDC/PD <> USDC then Num of records end).

Hope it helps

• ###### 5. Re: Headache for me - (sum of records based on combination of two columns)Dont know How to use this Calculation

I don't know how you want to show them with column headers,

Maybe playing around with fix, exclude, include LOD will be needed?

Thanks,

Shin

[Count Meet Federal]

{fixed [Federal Indicator]: sum(

if ([Federal Indicator]="Federal" )

then [Number of Records] else 0 end

)}

[Count Meet Federal + exclusion]

{fixed [Federal Indicator],[USDC/PD]: sum(

if ([Federal Indicator]="Federal" and [USDC/PD] <>"USDC" and [USDC/PD] <>"USDC-onsite")

then [Number of Records] else 0 end

)}

• ###### 6. Re: Headache for me - (sum of records based on combination of two columns)Dont know How to use this Calculation

I think you have got my requirement. You have shown the correct Result  but I want both the calculation on One calculated Field.Because I need to use all Measure Values as a Filter and I cant Use Measure Name as a Filter.Let me explain it one More Time.

I want a bar chart exactly as you demonstrated in your Above picture but with little change  it should not be segregated by three dimension .

In Column shelf lets put Number of Records and in Row shelf lets add a Dimension which should show like below

Just trying an example

Dimesnion Name - Type of visualization ( Barchart Values)

Note: The below values should appear in single calculated Field so that I can use it as Filter

Commercial - Barchart Diagram(100)

Federal- Barchart Diagram (90)

US GLS India- Barchart Diagram - (80)

Federal - (As per your First calculation/)- Barchart diagram - (70)

Federal without USDC-(As per your First calculation/)- Barchart diagram - (60)

{fixed [Federal Indicator]: sum(

if ([Federal Indicator]="Federal" )

then [Number of Records] else 0 end

)}

[Count Meet Federal + exclusion]

{fixed [Federal Indicator],[USDC/PD]: sum(

if ([Federal Indicator]="Federal" and [USDC/PD] <>"USDC" and [USDC/PD] <>"USDC-onsite")

then [Number of Records] else 0 end

)}

1 of 1 people found this helpful
• ###### 7. Re: Headache for me - (sum of records based on combination of two columns)Dont know How to use this Calculation

How about to use parameter instead of trying to use filter, although you cannot pick multiple item...??

Thanks,

Shin

• ###### 8. Re: Headache for me - (sum of records based on combination of two columns)Dont know How to use this Calculation

Hi Shin.

I was able to achieve finally what I am looking for.

Here the solution

But Now I am in another problem.Would u like to have a look and show some trick to achieve the same .