Inputsum and inputfield and unaggregated calculations

jonaguada — Thu, 02 Feb 2017 18:46:40 GMT

I'm using Inputfield and Inputsum to allow interactvity in a table chart. Inputsum works at an aggregated level, but I want to use the input value to run a calculation at a detailed record level and then aggregate the output. I am using the Inputfield to allow the end user to change their Minimums.

The example......

I'm using the following table to show the calculation that I'm attempting to perform with minimum being used as the input column.

ID	Client	Revenue	Discount	Minimum
1	A	$100	85%	$20
2	A	$100	75%	$5
3	B	$200	25%	$35
4	B	$300	90%	$50
5	B	$100	50%	$45

The formula is sum the greater of the following two

Discounted Revenue = Revenue - (Revenue * Discount)
Minimum

For the Chart above the correct sum would total to $295. The calculation is shown below.

IDs 1 and 4 use the minimum since this is the greater of the two numbers

IDs 2,3,5 use the Discounted Revenue since this is the greater of the two numbers

ID	Client	Revenue	Discount	Minimum	Discounted Revenue	Which is greater? The Discounted Revenue or Min
1	A	$100	85%	$20	100-85 = $15	$20
2	A	$100	75%	$5	100-75 = $25	$25
3	B	$200	25%	$35	200-50 = $150	$150
4	B	$300	90%	$50	300-270 = $30	$50
5	B	$100	50%	$45	100-50 = $50	$50
Total		$800			$270	$295

I keep getting the $270 (shown above) but this is the wrong answer. The correct answer would be the $295 when minimums area accounted for. I've attached a sample app that uses this example.

The key here is that the end user needs to be able to change their minimums. !

Re: Inputsum and inputfield and unaggregated calculations

Gysbert_Wassenaar — Thu, 02 Feb 2017 19:20:53 GMT

Perhaps this: rangemax(sum(Discount*Revenue),InputSum(InputMinimum))

Re: Inputsum and inputfield and unaggregated calculations

jonaguada — Fri, 03 Feb 2017 03:11:38 GMT

Thanks,

That just about did it. I played with it and the following got me what I needed. I needed to treat the InputMinimum field as a field and the place where I enter the data needed to have the formula updated to InputSum(InputMinimum,'=').

The final formula is .....

sum(rangemax(Revenue-(Discount*Revenue),InputMinimum))

topic Re: Inputsum and inputfield and unaggregated calculations in QlikView

Inputsum and inputfield and unaggregated calculations

Re: Inputsum and inputfield and unaggregated calculations

Re: Inputsum and inputfield and unaggregated calculations