Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

JustinDallas
Valued Contributor II

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

Re: Group by in chart

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

4 Replies

Re: Group by in chart

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
Valued Contributor II

Re: Group by in chart

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.

Re: Group by in chart

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
Valued Contributor II

Re: Group by in chart

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

Community Browser