11 Replies Latest reply on Sep 16, 2018 1:18 PM by Deepak Rai

Calculated Field based on Dimension Criteria

Hello,

Quick question. I'll try to explain it without TW.

I have created this formula first: IF [Method]="Scn" THEN COUNTD([BF SN]) END --- it says Cannot mix aggregate  and non-aggregate comparisons

Then, I put ATTR in front of [Method] : IF ATTR [Method]="Scn" THEN COUNTD([BF SN]) END - it works but only if I have Dimension in Rows/Columns. If I remove this Dimension from sheet it returns blank.

So, I need this formula to return me a value no matter if I have this Dimension ("Methos") on Sheet or no.

Is it possible?

Thanks!

• 1. Re: Calculated Field based on Dimension Criteria

Hi Mila,

You need Dimension in view. Drag your Dimension to Detail instead of Rows.

Try This Way:

IF [Method]="Scn" THEN ([BF SN]) END

Then you can Right Click and From Measure>COUNTD

Or you may write this

COUNTD(IF [Method]="Scn" THEN ([BF SN]) END)

Then you can Right Click and From Measure>COUNTD

Thanks

Deepak

• 2. Re: Calculated Field based on Dimension Criteria

I forgot to mention that  [BF SN] is Calculated Field so the second formula doesn't work in this format.

• 3. Re: Calculated Field based on Dimension Criteria

1. COUNTD(BF SN) - first Calculated Field

2. COUNTD(IF [Method]="Scn" THEN ([BF SN]) END) - Second Calculated Field (doesn't work now)

3. Need one more Calculated field which will be: 2/1 (Second Calculated Field / First Calculated Field) in order to get %.

Thanks a lot!

• 4. Re: Calculated Field based on Dimension Criteria

You need LOD for this...If You can attach workbook, I can Show you .

• 5. Re: Calculated Field based on Dimension Criteria

I have attached a workbook.

10Q

• 6. Re: Calculated Field based on Dimension Criteria

Here it is:

Ratio

Thanks

Deepak

If It Helps, Pl Mark It Helpful and CORRECT to Close Thread. The CORRECT button is in Main Thread under my Reply, not in email

1 of 1 people found this helpful
• 7. Re: Calculated Field based on Dimension Criteria

Now, If I put Week to view data per each Week don't have correct %.

• 8. Re: Calculated Field based on Dimension Criteria

Modify Calcs:

Thanks

Deepak

• 9. Re: Calculated Field based on Dimension Criteria

Like Yours

Thanks

Deepak

• 10. Re: Calculated Field based on Dimension Criteria

This works, but one more question.

I mean, is it possible to create Calculated Field that will work in any situation?

• 11. Re: Calculated Field based on Dimension Criteria

May be You want This ?

Thanks

Deepak

Please Close The Thread By Marking The Replies as Helpful and CORRECT