Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I apologize if this is a really basic question. I am scheduled to go to Designer training next month.
I need to create a chart that shows sales rep activity based on certain transactions, not all transactions. I think what I need to do is created a calculated dimension. When creating a chart, in the dimensions window I've selected sales rep. I also need to restrict the transaction field to certain values. This is where I'm stuck and I'm not sure of the syntax. I need the transaction_id field to be restricted to the following values:
BOOK_WIRE_1LINK
WIRE_DOMESTIC_1LINK
WIRE_INTERNATIONAL_1LINK
ADVANCE_1LINK
Any help would be greatly appreciated.
Thanks,
Shannon
Is the transaction_id also a dimension? If so, the calculated dimension would look like this:
if(match(transaction_id,'BOOK_WIRE_1LINK', 'WIRE_DOMESTIC_1LINK', 'WIRE_INTERNATIONAL_1LINK', 'ADVANCE_1LINK'),transaction_id)
However, I suspect the performance wouldn't be very good. Also, you might not have transaction_id as a dimension. Either way, I would instead handle it with set analysis in the expressions. For instance, if you had this expression in the chart:
sum(Amount)
You would change it to this:
sum({<transaction_id*={'BOOK_WIRE_1LINK', 'WIRE_DOMESTIC_1LINK', 'WIRE_INTERNATIONAL_1LINK', 'ADVANCE_1LINK'}>} Amount)
That should be very efficient.
Transaction ID is a dimension. The 2 dimensions are sales rep and transaction ID. The measure is the transaction count. I'm going to try your first approach and see if it gets me what I need.
Thanks,
Shannon
What I'm trying to do is something like:
If TRANSACTION_ID IN ''BOOK_WIRE_1LINK', 'WIRE_DOMESTIC_1LINK', 'WIRE_INTERNATIONAL_1LINK', 'ADVANCE_1LINK' then SUM(TXN_COUNT) else 0
I think I need to go to Chart Properties -> Expressions and edit the expression but I'm not sure of the syntax.
Any help would be greatly appreciated.
Thanks,
Shannon
Yes, edit the expression. This should work:
if(match(TRANSACTION_ID, 'BOOK_WIRE_1LINK', 'WIRE_DOMESTIC_1LINK', 'WIRE_INTERNATIONAL_1LINK', 'ADVANCE_1LINK'), sum(TXN_COUNT), 0)
I edited the expression and pasted in
if(match(SYS_ID, 'BOOK_WIRE_1LINK', 'WIRE_DOMESTIC_1LINK', 'WIRE_INTERNATIONAL_1LINK', 'ADVANCE_1LINK'), sum(TXN_COUNT), 0)
and my bar chart went from displaying data to saying 'No data to display' although I know that there is data there. Instead of TRANSACTION_ID its actually SYS_ID.
OK, now we're back to my earlier question - is that field (SYS_ID now) a dimension in the table?
I'm going to guess that the answer is now "no" intead of the previous "yes". If so, then you can have more than one SYS_ID per line of your chart, which will be treated as null in the comparison, so you'll get 0, so all rows will be suppressed, so I can see it saying "no data to display" though I'm not certain that's the problem.
I suggest trying the expression I gave earlier, with minor modifications to use your real field names:
sum({<SYS_ID*={'BOOK_WIRE_1LINK', 'WIRE_DOMESTIC_1LINK', 'WIRE_INTERNATIONAL_1LINK', 'ADVANCE_1LINK'}>} TXN_COUNT)
SYS_ID is not a dimension. The sum of the TXN_COUNT related to the SYS_IDs I am trying to restrict is what I am trying to measure. The chart will not be displaying each value for SYS_ID. The only dimension is SALES_REP with the measure (Y axis) being the sum of the TXN_COUNT for each SALES_REP.
I'll try what you've posted and confirm that the numbers are correct.
Thanks,
Shannon