Expressions

[amcontractors]

I have two advanced reporting issues that I would like assistance with.

1. In the first report, I am trying to type out an expression to represent total costs applied for the time period specified to customer:jobs. The total sales column is correct and using a similar expression as costs, however only the sales column matches QuickBooks. The cost column does not match QuickBooks for any year. What expression should I put in to capture total costs?

This is in the Job Activities Over Time Starter Report that I made some changes to. 

These are the expressions I tried so far that have not provided results: 

A. sum({<[Transactions.Header Or Line]={'Header'}, Transactions.Pending={0}, [Transactions.Txn Type]= {'Bill','Item Receipt','Vendor Credit','CHK','CreditCardCharge','CreditCardCredit','General Journal','Paycheck'}>}[Transactions.Amount With Sign])

B. Sum ({<$(vExprCOGS)>}[Transactions.Amount With Sign])

The defined dimensions are as follows: 

- Year, Month, Transactions.Customer Full Name

2. I am trying to add a column showing the dollar value of the last payment made for the customer:job on a table I am creating to show transaction.customer full name followed by the last payment date from the customer and the last amount paid. The date showing up as last payment date is correct, but I can't figure out an expression to use to bring that up. 

This is the expression I have for the last payment date which works: 

[CustomerImportantDates.Last Payment Date]

Here is the dimensions for that table:

Transactions.Customer Full Name

Here is the expression I tried to pull the balance that did not work: 

only({<[CustomerImportantDates.Last Payment Date],[Transactions.Txn Type]={'Invoice'},Transactions.Paid={1},[Transactions.Header Or Line]={'Header'}>}[Transactions.Amount With Sign])

Please let me know which expressions I need to use. 

[dena_reavis]

On the first one, are you expecting the total cost column to = total sales column? If so, the difference would likely be in the expressions and perhaps you can't see it when one of the expressions is using a variable. Try removing the label on the expression (on the one that has the variable in the set analysis) in your pivot table and on the presentation tab change the Multiline settings to wrap header rows to 5 or 6 , so you can see it. What this does is makes Qlik show you the expression as the label and you can see what it is expanding to, and you can see the difference in the expressions. 

On the second one,  you probably should break it apart one piece at a time. Then you can assemble them together using the concatenation symbol &, like: Expression & Expression &' Customer:' and so on. Start with something like Max([CustomerImportantDates.Last Payment Date]) and build the others in separate expressions. It just makes it easier to troubleshoot separate parts. You can assemble them easily when you are sure they are working.

Hope this is helpful.