Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

Not applicable

Set Analysis Question?

Hello all-

I am trying to calculate a field for a subset of records using data from records that are not selected. I think this can be done using set analysis but I haven't been able to figure it yet. It seems like everything I try creates records in my table for the purchases.

For each sale in the below table, I am trying to calculate the pro-rata cost as the sum of the purchase costs * quantity for that contract / sum of purchase quantities.  In my example below, I am calculating the Cost for Contract 51 HourEnding14 as (934.67 + 1652.38) * 100 / (53.22 + 95.78)

Example Data:

sample data.jpg


Desired Output:

desired output.jpg

Is there away to do this without creating a separate table for the total purchase costs and quantities?

Thanks for your help -

Tags (2)
1 Solution

Accepted Solutions
MVP
MVP

Re: Set Analysis Question?

Hi John,

try

=sum({<BUYSELL={B}>} total<DATE, HOURENDING> COST)*only({<BUYSELL={S}>}QUANTITY)

  / sum({<BUYSELL={B}>} total<DATE,HOURENDING> QUANTITY)

as expression in your pivot table chart.

Regards,

Stefan

2 Replies
MVP
MVP

Re: Set Analysis Question?

Hi John,

try

=sum({<BUYSELL={B}>} total<DATE, HOURENDING> COST)*only({<BUYSELL={S}>}QUANTITY)

  / sum({<BUYSELL={B}>} total<DATE,HOURENDING> QUANTITY)

as expression in your pivot table chart.

Regards,

Stefan

Not applicable

Set Analysis Question?

Stefan - That's exactly what I needed!  I was not familiar with the only function.  Thanks for your quick response.

- John

Community Browser