Announcements
[WEBINAR] Accenture & Qlik: Accelerating BI Migration to SaaS with Qlik on Dec 13th: REGISTER
cancel
Showing results for
Did you mean:
Contributor III

## Inputsum and inputfield and unaggregated calculations

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.

IDClientRevenueDiscountMinimum
1A\$10085%\$20
2A\$10075%\$5
3B\$20025%\$35
4B\$30090%\$50
5B\$10050%\$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

IDClientRevenueDiscountMinimum

Discounted Revenue

Which is greater? The Discounted Revenue or Min

1A\$10085%\$20100-85 = \$15\$20
2A\$10075%\$5100-75 = \$25\$25
3B\$20025%\$35200-50 = \$150\$150
4B\$30090%\$50300-270 = \$30\$50
5B\$10050%\$45100-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. !

1 Solution

Accepted Solutions

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

talk is cheap, supply exceeds demand
2 Replies

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

talk is cheap, supply exceeds demand
Contributor III
Author

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))

Community Browser