Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Announcements

[WEBINAR] Accenture & Qlik: Accelerating BI Migration to SaaS with Qlik on Dec 13th:__ __**REGISTER **

- Qlik Community
- :
- All Forums
- :
- QlikView App Dev
- :
- Inputsum and inputfield and unaggregated calculati...

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page

Showing results for

jonaguada

Contributor III

2017-02-02
01:46 PM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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.

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. !

900 Views

1 Solution

Accepted Solutions

2017-02-02
02:20 PM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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

2 Replies

2017-02-02
02:20 PM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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

jonaguada

Contributor III

2017-02-02
10:11 PM

Author

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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