Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Support Cases coming to Qlik Community Oct. 4! Start chats, open cases, explore resources: READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Rows show 0 but there is a total

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

5 Replies
Not applicable
Author

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

Not applicable
Author

Hi,

What is your Dimension or Dimensions?

An example will be very usefull.

Best regards.

Not applicable
Author

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

Not applicable
Author

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

Not applicable
Author

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.