Find Max Value for two selected value

[#1]

Hi friends,

I am attaching my application,where I want to view the Max value for two selected interval,if anyone get any idea please fix this its really urgent.

Regards

Bhawna    

[MK_QSL]

Can you provide little more idea about your requirements?

[juleshartley]

First comment that I'd make is that your data model looks a bit odd. What is the point of the 'Value', 'Value 1' etc. fields?

I would 'crosstable' the data so that you have 'interval' as a field with values 1-3 Month etc. And then a single 'Value' field.

ie.

Product          Interval     Value

P1               1-3 Month     200

P2               1-3 Month     500

P1               4-6 Month     300

Then your calculations are a lot easier.

[rubenmarin]

you searching for?:

=MaxString(Interval)

[#5]

See,

in first image when no selection is made,then it show max value as 700 which is maximum in all month bucket,

but in second image when I select more than one interval it wont show any data,I want this data.

[juleshartley · Accepted solution]

If you don't want to change the data model as suggested (please do) then you can use something like:

= If(GetSelectedCount(Interval)=0,

RangeMax( max(Value),max(Value1),max(Value2) ) ,

RangeMax(

SubStringCount(GetCurrentSelections(Interval),'1-3 month' )* max(Value),

SubStringCount(GetCurrentSelections(Interval),'4-6 month' )* max(Value1),

SubStringCount(GetCurrentSelections(Interval),'6-9 month' )* max(Value2) ) )

NB: I've only done up to value2

[#7]

actually customer has his data in this format,where interval like 1-3 month showing only 1 for yes and 0 for no and value is non zero only for 1 for respective interval month,I cant make changes in their data model

[MK_QSL]

Change your expression as

if(Interval='1-3 month',max({<[1-3 month]={'1'}>}Value),

if(Interval='4-6 month',max({<[4-6 month]={'1'}>}Value1),

if(Interval='6-9 month',max({<[6-9 month]={'1'}>}Value2),

if(Interval='9-12 month',max({<[9-12 month]={'1'}>}Value3))

)

))

[juleshartley]

See my answer above then... sorry - they went in a funny order

[#10]

Thank You julian