Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
On May 18th at 10AM EDT we will answer your QlikView questions live. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
jonaguada
Contributor II
Contributor II

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
Gysbert_Wassenaar

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


talk is cheap, supply exceeds demand

View solution in original post

2 Replies
Gysbert_Wassenaar

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


talk is cheap, supply exceeds demand

View solution in original post

jonaguada
Contributor II
Contributor II
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))