Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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, ', ' )
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.
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.
that is simply the sale price (IDSALP) and the cost (IDCOSP) per detail line.
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 ?