3 Replies Latest reply on Nov 21, 2018 2:45 PM by Deepak Rai

Using Aggregated Data in If Statements

I have been trying to resolve a problem with a calculated field. I have a set of aggregated data from which I calculate certain factors.

The specific fields are:

Number of Products:      self explanatory

Total Price:                     This is the aggregated Total for all the selling prices in the product count for that period.

Total Full Price:              This is the aggregated Total for all the full prices in the product count for that period (this equals the above field if the discount is 0).

I then calculate:

Price:                              I divide Total Price by Number of Products. This gives me the average selling price per product.

Full Price:                       I divide Total Full Price by Number of Products. This gives me the average selling price per product.

Discount %:                    I have a similar formula here

Formula: ((sum([Total Full Price])/sum([Number of Products]))-(sum([Total Price])/sum([Number of Products])))/(sum([Total Full Price])/sum([Number of Products]))

The problem I have is that I can't use these formulas in an if statement. I need to do the calculation so that I'm applying the discount formula for when [Category] = XXX

I can't use an aggregated measure and an if statement together. Does anyone have any advice on how to resolve?

• 1. Re: Using Aggregated Data in If Statements

Would it be possible for you to provide some sample data, and the expected output from it? It will make it easy to suggest the appropriate solution.

• 2. Re: Using Aggregated Data in If Statements

Hi Hari,

I would but unfortunately I can't. The data is sensitive info and was supplied to me already aggregated. I'm not familiar with how to aggregate data so I can't complete the process myself.

• 3. Re: Using Aggregated Data in If Statements

Disd you try using this:

If ATTR( [Category] )= XXX

THEN  This

ELSEIF ATTR( [Category] )= YYY

THEN that

Else

Something

END