Rows show 0 but there is a total

[#1]

Hello Everyone

I have a pivot table.

One of the expression iam writing is

sum({<[Schedules ATTRIBUTE1] = {'R'},[Revenue Type] = {'REV'}>}[Schedules ATTRIBUTE4])

In this expression column. It shows me all rows 0. But shows a total amount for me which is the correct amount.

What could be the reason and what is the best possible way to fix it?

Thanks

[#2]

Strange!!!.. Can you post the sample file.

[#3]

Hi,

What is your Dimension or Dimensions?

An example will be very usefull.

Best regards.

[#4]

Hello Ramko

I think my expression is causing the issue

The expression is

(sum({<[Schedules ATTRIBUTE1] = {'R'},[Revenue Type] = {'REV'}>}[Schedules ATTRIBUTE4]))*$(vf-Deferred Revenue Null Activity)

Note : But $(vf-Deferred Revenue Null Activity) is an expression in itself which is

(if($(vf-Deferred Revenue Beginning Balance)=0 and $(vf-BBPeriodGross)=0 and $(vf-BBAccruals)=0 and

  $(vf-Period Gross Changes Adjustments)=0 and $(vf-BBPDChanges)=0 and $(vf-BBCurrent Recognitions)=0

  and $(vf-BBCurrentClaims)=0 and $(vf-Current Releases)=0 and $(vf-BBAdjustments)=0 and ($(vf-Deferred Revenue Beginning Balance)+$(vf-BBPeriodGross)+$(vf-BBAccruals)+$(vf-Period Gross Changes Adjustments)+$(vf-BBPDChanges)+$(vf-BBCurrent Recognitions)+$(vf-BBCurrentClaims)+$(vf-Current Releases)+$(vf-BBAdjustments))=0

  ,0,1))

Note: The variables in this expression are various sum(if (....)) expressions.

Please advise

[#5]

Hello Oswaldo

I think my expression is causing the issue

The expression is

(sum({<[Schedules ATTRIBUTE1] = {'R'},[Revenue Type] = {'REV'}>}[Schedules ATTRIBUTE4]))*$(vf-Deferred Revenue Null Activity)

Note : But $(vf-Deferred Revenue Null Activity) is an expression in itself which is

(if($(vf-Deferred Revenue Beginning Balance)=0 and $(vf-BBPeriodGross)=0 and $(vf-BBAccruals)=0 and

  $(vf-Period Gross Changes Adjustments)=0 and $(vf-BBPDChanges)=0 and $(vf-BBCurrent Recognitions)=0

  and $(vf-BBCurrentClaims)=0 and $(vf-Current Releases)=0 and $(vf-BBAdjustments)=0 and ($(vf-Deferred Revenue Beginning Balance)+$(vf-BBPeriodGross)+$(vf-BBAccruals)+$(vf-Period Gross Changes Adjustments)+$(vf-BBPDChanges)+$(vf-BBCurrent Recognitions)+$(vf-BBCurrentClaims)+$(vf-Current Releases)+$(vf-BBAdjustments))=0

  ,0,1))

Note: The variables in this expression are various sum(if (....)) expressions.

Please advise

[#6]

Wow...

When I have several bussines rules, I create one by one in script and rty to use a boolean way:

Example: My Sales are sum of field Amount but just use account 1,2,700,900,2100 and DocType = RA,RE,RS and CostCenter <> 300, 400 and 900, and OrderDate < CloseDate and Product <>1000 and Customer Status = OK

Step 1.- in script create each rule.

MyTable:

Load

     DocID,

     Amount,

     Account,

     OrderDate,

     CloseDate,

     Prouct,

     CustomerID,

     CustomerStatus

     IF(Match(Account,1,2,700,900,2100),1,0) as IsAccountOK,

     IF(Match(DocType,RA,RE,RS),1,0) as IsDocOK,

     IF(Match(CostCenter,300,400),0,1) as IsCostCenterOK,

     IF(OrderDate < CloseDate,1,0) as IsDateOk,

     IF(Match(Product,1000),0,1) as IsProductOK,

     IF(Match(CustomerStatus,OK),1,0) as IsCustStatusOK,

From MyData.qvd;

Step 2.- Build your expression in charts or into Variable.

Sum(Amount * IsAccountOK * IsDocOK * IsCostCenterOK * IsDateOK * IsProductOK * IsCustStatusOK)

So.. using this "boolean way" you can build more easier complex expressions.

How does it work?

Imagine a Simple rule Sum Amount where Type = Sales

IF(Match(Type,Sales),1,0) as IsSales // In script this IF

Sum(Amount * IsSales)

See above like a table

Amount     | Type     | IsSales     | Sales *IsSales

100               Sales          1               100

100               Notes          0               0

100               Notes          0               0

100               Sales          1               100

100               Sales          1               100

-------------------------------------------------------------------

500                                                   300

I hope give you an idea.

Best regards.