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: 
JustinDallas
Specialist III
Specialist III

Group by in chart

Hello Everyone,

I have a table that looks like this.

AOW_3.PNG

As you can see, there are two rows caused by the fact that I've concatenated two tables, Revenues and Expenses.  These tables have to be concatenated for Calendar reasons.  If you notice, you'll see that the four columns to the right are the same, but the leftmost ones are different. This is because some of these fields ONLY exist on the initial Revenue table and others exist ONLY on the Expense table before concatenation.

Is there a way I can tell the chart to "combine" both lines?

In SQL, I would do something like SELECT VENDORID, Expense Date, Revenue Date, Document Number, TRXDSCN FROM Blah GROUP BY [Invoice Number].

I feel I have to use AGGR somehow, but it wouldn't be based off a MAX, or MIN that I've seen in examples.

Here is a test script.

DummyData:

LOAD * Inline

[

VENDORID,Expense Date,Revenue Date,Document Number,TRXDSCRN, Bill Date,Bill To ID,Invoice Number,Order Number

HARMON,08/29/2017,,248986,248986,09/13/2017,SPECAR01,186329A,186329

,,09/13/2017,,,09/13/2017,SPECAR01,186329A,186329

]

;

Any help is greatly appreciated.

1 Solution

Accepted Solutions
sunny_talwar

I mean you can probably still show Revenue Date as an expression, do you really have to show it as a dimension? I don't have too much details

View solution in original post

4 Replies
sunny_talwar

It seems that the Revenue Date is the only one which might be causing the issue? Do you need Revenue Date to be one of the chart dimensions?

JustinDallas
Specialist III
Specialist III
Author

Yes, I need the Revenue Date, because one of the questions the chart is to answer is "Which Expenses have we not received Revenue for".  The absence of the Revenue Date will show that.

There are other ways to accomplish the same goal, but this data structure seems to want to contort itself into a straight-jacket no matter what I've tried.

sunny_talwar

I mean you can probably still show Revenue Date as an expression, do you really have to show it as a dimension? I don't have too much details

JustinDallas
Specialist III
Specialist III
Author

Oh shiggity!  I think you did something!   Why does this work so differently?  When I think "Dim" I think, "regular value".  When I think measure, I think  "torture a value with functions". 

Why does this have the side-effect of doing this?

AOW_4.PNG