topic Re: Calculated Demension in QlikView

Calculated Demension

Wed, 25 Nov 2020 16:16:04 GMT

I'm trying to create a field "Take Rate" that is the sum of the number of parts sold/sum of the number of vehicle sold. As of right now, the take rate field is the sum of the each part sold. Is there anyway to reference another table to use as the denominator or is there anyway to reference the max value in the take rate field I currently have?

Re: Calculated Demension

vishsaggi — Mon, 24 Jul 2017 19:34:04 GMT

May be this:

= Max(TOTAL Sum(number of parts sold) / (sum of the number of vehicle sold))

Re: Calculated Demension

Mon, 24 Jul 2017 19:46:31 GMT

I'm trying to reference the max in the Take Rate field and use it as the denominator in another calculated dimension maybe? Is there anyway to do this? Or is there anyway to reference another table within a calculated dimension? I've looked into the aggr function and creating variables but haven't been able to figure this out.

Re: Calculated Demension

vishsaggi — Mon, 24 Jul 2017 19:48:31 GMT

Can you give me some sample data and your expected output? It will be easy to work on..

Re: Calculated Demension

sonkumamon — Mon, 24 Jul 2017 20:10:12 GMT

Hi Brett

The Take Rate field is calculated within the chart or in the load script?

Re: Calculated Demension

Mon, 24 Jul 2017 20:12:51 GMT

As of now it is calculated within the chart

Re: Calculated Demension

Mon, 24 Jul 2017 20:14:35 GMT

What would be the syntax to put it into the load script?

Re: Calculated Demension

Mon, 24 Jul 2017 20:43:48 GMT

File is uploaded to original post

Re: Calculated Demension

vishsaggi — Mon, 24 Jul 2017 20:51:59 GMT

will look into it once i go home.

Re: Calculated Demension

sonkumamon — Tue, 25 Jul 2017 05:21:38 GMT

I don't know how your model is constructed, but for example you can create an aggregate table for the sum of parts sold to create the Take Rate Field.

For example:

Take_Rate:

LOAD

ProductHierachyID,

MaterialID,

SUM(SoldParts) AS TakeRate

FROM Products

GROUP BY ProductHierachyID, MaterialID

;

Re: Calculated Demension

vishsaggi — Tue, 25 Jul 2017 13:48:10 GMT

This is what i can see in your qvw file for take rate. So, what is that you are trying here? And what is your expected output?

Sum({<[Sales Document Type]={'ZVHL'},[Document type]={'RV'}>}[Quantity])

Re: Calculated Demension

Tue, 25 Jul 2017 13:51:43 GMT

Vishwarath,

Thank you for the help. I was able to solve this problem by writing the Sum statement into the load script.

Re: Calculated Demension

vishsaggi — Tue, 25 Jul 2017 13:52:42 GMT

Sounds good.