topic measure not working in QlikView

measure not working

Wed, 18 Nov 2015 20:01:21 GMT

HI I am trying to do the following. Our invoice table has the sale price and cost price. I would like to show the dimensions according to a formula like cost/price showing the largest percent of profit. On the measure part, I would want just that order#. but for measure part, it has functions only not just to show the order #. Or if I want to have a formula as the dimension like this:

x=IDSALP-IDCOSP

y= x / idcost

it is not clear how to enter this.

Re: measure not working

JonnyPoole — Wed, 18 Nov 2015 20:42:38 GMT

You can show the dimension values in an expression. You should still use aggreagation functions to do this. Common ones are concat() and only()

Here is an example where an the 2nd column is an expression: concat( distinct Country, ', ' )

Re: measure not working

Wed, 18 Nov 2015 20:51:19 GMT

ok but lets say the formula is more involved, i guess make this a function on the server?

x=IDSALP-IDCOSP

y= x / idcost

it is not clear how to enter this.

Re: measure not working

JonnyPoole — Wed, 18 Nov 2015 21:29:13 GMT

All Charts represented aggregated summaries and the level of aggregation depends on the granularity of the dimensions. If you want to report transactional level values, add transactionID as a dimension. If you want to report customer level information then add customer as a dimension. All this to say that you SHOULD use numeric aggregation functions in your expressions

Sum(IDSALP) - SUM(IDCOSP) will sum up the detail values of each per each dimension value and do a subtraction.

Sum( IDSALP-IDCOSP) will do a subtraction at the record level for each dimension value and sum the results.

I have no clue what IDSALP or IDCOSP actually represent so this may be a nonsensical situation. You will have to impart what is really going on.

Re: measure not working

Wed, 18 Nov 2015 21:31:34 GMT

that is simply the sale price (IDSALP) and the cost (IDCOSP) per detail line.

Re: measure not working

JonnyPoole — Thu, 19 Nov 2015 17:34:15 GMT

If you have 'detail ID' in the chart as a dimension then you can just write

IDSALP/IDCOSP

that should work.

If you have 2 actual records for same 'detail ID' than qlik will need to know which calculation to use to aggregate those 2 records together to report one result for that detail ID. You might use avg() etc... to do that, but consider that possible case.

Or you could just check to see how many records there are

if ( count( [DetailID]) = 1 , IDSALP/IDCOSP )

This will do the division if there is only 1 record. If there are more it will return a NULL '-' in the UI.

Does that help ?