Why is my expression showing 0?

[#1]

When I have the following calculated dimension

=IF((WeeksAgo='1' AND Revenue='0'),Market,)

Why do my values for this expression all appear as 0?

SUM({1<CalendarDate1={'>=31/3/14'}>}Revenue)

When I set my dimension simply as 'Market', the same expression above calculates the totals fine.

[tresesco]

Because the dimension is having those Markets where Revenue is 0.

[mdmukramali]

Dear ,

Take the dimension Market.

Expression:

SUM({<CalendarDate1={'>=31/3/14'}, WeeksAgo={1],Revenue={0}>}Revenue)

else Can you attach the sample file.

Thanks

[#4]

the revenue is not zero for the Fiscal YTD to date though. Only for last week

So there should be revenue there

[tresesco · Accepted solution]

Perhaps, you need p():

Dimension:Market

Expression: =SUM({<CalendarDate1={'>=31/3/14'}, Market=P({<WeeksAgo={1],Revenue={0}>})>}Revenue)

[#6]

That worked! Thanks

What is P? How does it work?

[tresesco]

Learn Set Analysis carefully, it's very powerful. P() is a part of set analysis called element function.

From Help:

An additional way to define a set of field values by
the use of a nested set definition.

In such cases, the
element functions P() and E() must be used, representing the element set of
possible values and the excluded values of a field, respectively. Inside the
brackets, it is possible to specify one set expression and one field, e.g. P({1}
Customer). These functions cannot be used in other expressions:

Examples:

sum( {$<Customer =
P({1<Product={‘Shoe’}>} Customer)>} Sales )
returns the sales for current selection, but only those
customers that ever have bought the product ‘Shoe’. The element function P( )
here returns a list of possible customers; those that are implied by the
selection ‘Shoe’ in the field Product.

sum( {$<Customer =
P({1<Product={‘Shoe’}>})>} Sales )
same as
above. If the field in the element function is omitted, the function will return
the possible values of the field specified in the outer assignment.