Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
jenmclean
Contributor III
Contributor III

Sum Credit/Debit if matching condition met

In this chart, I need to combine (calculate) for a net total if the number in the RMA/PO# column and the Vendor Inv column match. This is an accounts payable function. So on the same check number there in an invoice and a debit and I want to combine these two lines for a Pmt Amt that would equal $240.42 as the information from this chart is exported into the accounting software. This would eliminate repetitive tasks for the employee.

How would I write an expression that would do this?setanalysis1.png

Message was edited by: Jennie Elliott I have added an example of what my end results should be Attached is what I am trying to achieve: If RMA/PO# = Vendor Inv, then Sum for a Net Payment (Invoice - Credit). I need to combine these matching lines into one line if those numbers match. Do I script this or write an expression?

12 Replies
JonnyPoole
Employee
Employee

Hi - I loaded a new 'ID' field that takes its value from the RMA/PO #  or the Vendor Inv field depending which is null().

Data:

LOAD Check,

    Date,

    RMA,

    [Vendor Inv],

    if( isnull(RMA),[Vendor Inv],RMA) as ID,

    E

FROM

(ooxml, embedded labels, table is Sheet1);

ID:

Load

  Distinct

  ID

resident Data;

Then i created a straight table with ID as a dimension (but hidden per presentation tab settings).

It gave me this for attached data sample.

Does this help ?

Capture.PNG

jenmclean
Contributor III
Contributor III
Author

I'll try that and let you know.

Thanks!

petter
Partner - Champion III
Partner - Champion III

Here is a variant using a calculated dimension (could be done in the load script thus removing the need for a calculated dimension), a pivot table, and partial sum on the [Date Entered] field. The expression is simply

Sum([Pmt Amt]). Have attached a sample app too.

2015-03-25 Credit Debet #1.PNG

The first table is just a Table Box to illustrate the raw table. The second table is a pivot with all dimensions expanded. The third table is just a copy of the second to show how it looks like when you "Collapse All" on the first dimension giving you hopefully exactly what you want your users to export to Excel...

jenmclean
Contributor III
Contributor III
Author

I cant' put it in a Pivot Table due to having to export all data into Great Plains accounting software. It must be in a straight table chart.

petter
Partner - Champion III
Partner - Champion III

Neither Export or pressing the XL icon on the title bar on the chart should work fine for Pivot Table... Can you explain in more detail?

petter
Partner - Champion III
Partner - Champion III

I mean "Both ... and ...." not Neither ....

petter
Partner - Champion III
Partner - Champion III

You can even automate the export from a button and calling a very simple Macro (which will run perfectly well

even on a QlikView server).

2015-03-25 Credit Debet #2.PNG

petter
Partner - Champion III
Partner - Champion III

Or if you prefer to do it in a load script so you actually get two new fields you can even do the Export via an Action/External/Export which demands to have fields with values to be able to export - I have also attached the lates QVW with the code/app:

2015-03-25 Credit Debet #3.PNG

jenmclean
Contributor III
Contributor III
Author

This might work better, I will try it out!

Thanks!