https://community.qlik.com/t5/App-Development/Display-daily-rate-of-highest-value-in-set/m-p/1968157#M79815 <P>Hi folks,</P> <P>My company rents out rooms at variable rates, and I'm building a report such that the sales team can quickly check "historically, what did we charge for this room in this week of the year"?</P> <P>So, the user selects a room and inputs their desired week.</P> <UL> <LI>A text box displays the average total rate for that room in that week (total rental revenue divided by number of relevant events). The expression looks like this: <UL> <LI>=ceil(sum({&lt;[Space Status]={'Contracted', 'Documented'}, [Event End Year]-={'2020', '2021'}, [Order Line Status]={'O'}, [Event End Week]={'$(vProposalWeek)'}, [Space Description]={'$(vProposalSpace)'}, [Order Department]={'01'}&gt;}[Item Amount])<BR />/count({&lt;[Space Status]={'Contracted', 'Documented'}, [Event End Year]-={'2020', '2021'}, [Order Line Status]={'O'}, [Event End Week]={'$(vProposalWeek)'}, [Space Description]={'$(vProposalSpace)'}, [Order Department]={'01'}&gt;}[Event Desc])))</LI> </UL> </LI> <LI>A second text box displays the average daily rate for that room in that week (total rental revenue divided by total duration of all the relevant events).&nbsp;The expression looks like this: <UL> <LI>=ceil(sum({&lt;[Space Status]={'Contracted', 'Documented'}, [Event End Year]-={'2020', '2021'}, [Order Line Status]={'O'}, [Event End Week]={'$(vProposalWeek)'}, [Space Description]={'$(vProposalSpace)'}, [Order Department]={'01'}&gt;}[Item Amount])<BR />/ceil(sum({&lt;[Space Status]={'Contracted', 'Documented'}, [Event End Year]-={'2020', '2021'}, [Order Line Status]={'O'}, [Event End Week]={'$(vProposalWeek)'}, [Space Description]={'$(vProposalSpace)'}, [Order Department]={'01'}&gt;}[Event Days]))), '#,##0')</LI> </UL> </LI> <LI>A third text box shows the highest total rate charged for that room in that week.&nbsp;The expression looks like this: <UL> <LI>=Max({&lt;[Space Status]={'Contracted', 'Documented'}, [Event End Year]-={'2020', '2021'}, [Order Line Status]={'O'}, [Event End Week]={'$(vProposalWeek)'}, [Space Description]={'$(vProposalSpace)'}, [Order Department]={'01'}&gt;}[Item Amount])</LI> </UL> </LI> </UL> <P>"vProposalWeek" is just a variable that stores the current selected week, and "vProposalSpace" stores the current selected space.</P> <P>Where I'm stuck is that I'd like to show a fourth text box which lists the highest daily rate charged i.e. the highest total rate, divided by the duration of the event in which that rate was charged.</P> Fri, 12 Aug 2022 10:50:00 GMT FionnM 2022-08-12T10:50:00Z https://community.qlik.com/t5/App-Development/Display-daily-rate-of-highest-value-in-set/m-p/1968157#M79815 <P>Hi folks,</P> <P>My company rents out rooms at variable rates, and I'm building a report such that the sales team can quickly check "historically, what did we charge for this room in this week of the year"?</P> <P>So, the user selects a room and inputs their desired week.</P> <UL> <LI>A text box displays the average total rate for that room in that week (total rental revenue divided by number of relevant events). The expression looks like this: <UL> <LI>=ceil(sum({&lt;[Space Status]={'Contracted', 'Documented'}, [Event End Year]-={'2020', '2021'}, [Order Line Status]={'O'}, [Event End Week]={'$(vProposalWeek)'}, [Space Description]={'$(vProposalSpace)'}, [Order Department]={'01'}&gt;}[Item Amount])<BR />/count({&lt;[Space Status]={'Contracted', 'Documented'}, [Event End Year]-={'2020', '2021'}, [Order Line Status]={'O'}, [Event End Week]={'$(vProposalWeek)'}, [Space Description]={'$(vProposalSpace)'}, [Order Department]={'01'}&gt;}[Event Desc])))</LI> </UL> </LI> <LI>A second text box displays the average daily rate for that room in that week (total rental revenue divided by total duration of all the relevant events).&nbsp;The expression looks like this: <UL> <LI>=ceil(sum({&lt;[Space Status]={'Contracted', 'Documented'}, [Event End Year]-={'2020', '2021'}, [Order Line Status]={'O'}, [Event End Week]={'$(vProposalWeek)'}, [Space Description]={'$(vProposalSpace)'}, [Order Department]={'01'}&gt;}[Item Amount])<BR />/ceil(sum({&lt;[Space Status]={'Contracted', 'Documented'}, [Event End Year]-={'2020', '2021'}, [Order Line Status]={'O'}, [Event End Week]={'$(vProposalWeek)'}, [Space Description]={'$(vProposalSpace)'}, [Order Department]={'01'}&gt;}[Event Days]))), '#,##0')</LI> </UL> </LI> <LI>A third text box shows the highest total rate charged for that room in that week.&nbsp;The expression looks like this: <UL> <LI>=Max({&lt;[Space Status]={'Contracted', 'Documented'}, [Event End Year]-={'2020', '2021'}, [Order Line Status]={'O'}, [Event End Week]={'$(vProposalWeek)'}, [Space Description]={'$(vProposalSpace)'}, [Order Department]={'01'}&gt;}[Item Amount])</LI> </UL> </LI> </UL> <P>"vProposalWeek" is just a variable that stores the current selected week, and "vProposalSpace" stores the current selected space.</P> <P>Where I'm stuck is that I'd like to show a fourth text box which lists the highest daily rate charged i.e. the highest total rate, divided by the duration of the event in which that rate was charged.</P> Fri, 12 Aug 2022 10:50:00 GMT https://community.qlik.com/t5/App-Development/Display-daily-rate-of-highest-value-in-set/m-p/1968157#M79815 FionnM 2022-08-12T10:50:00Z