Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Strange!!!.. Can you post the sample file.
Hi,
What is your Dimension or Dimensions?
An example will be very usefull.
Best regards.
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
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
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.