topic Calculated dimensions in QlikView

Calculated dimensions

Mon, 08 Nov 2010 22:44:55 GMT

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

Calculated dimensions

johnw — Mon, 08 Nov 2010 22:54:46 GMT

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.

Calculated dimensions

Mon, 08 Nov 2010 23:02:54 GMT

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

Calculated dimensions

Tue, 09 Nov 2010 17:03:42 GMT

Calculated dimensions

johnw — Tue, 09 Nov 2010 17:23:37 GMT

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)

Calculated dimensions

Tue, 09 Nov 2010 17:44:46 GMT

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.

Calculated dimensions

johnw — Tue, 09 Nov 2010 18:23:31 GMT

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)

Calculated dimensions

Tue, 09 Nov 2010 18:31:24 GMT

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