Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello Everyone,
I have a table that looks like this.
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.
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
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?
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.
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
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?