expression going wrong in kpi

[kunkumnaveen]

Hello all ,

i am trying to show current score in guage chart

expression :=If(SubStringCount(Concat(DISTINCT LOCATION, ', '), 'CHANDERIA') = 1,FirstSortedValue(OVERALL_SCR,-ADATE), 0)

and in presentation tab

under

text in chart:

=If(SubStringCount(Concat(DISTINCT LOCATION, ', '), 'CHANDERIA') = 1,FirstSortedValue(OVERALL_SCR,-ADATE), 0)

both the expression giving me the wrong answer

i am getting 41 as current score(wrong answer)

it has to be 71 (correct)

wat going wrong please help me

[kunkumnaveen]

can any one help me with the above requirement

please

[mightyqlikers]

hi,

please share sample qvw.

[Digvijay_Singh]

I checked the expression, its working fine with small sample, can you share your data in excel to verify.

I found your date is in MM/DD/YYYY format, hope your default format is this only, else it will give wrong result. It was giving earlier 41 but when corrected it as per my format, it gave 71.

[Anonymous]

plz share the sample data.....

[kunkumnaveen]

I am trying to send sample qvw ..but its not letting me because the file is around 12 mb...

so exactly do i need to do

actually the date i got was YYYY-MM-DD

i changed it in to mm/dd/yyyy

at script level

......

when i selection that particular location it is giving me 71

but when i press clear all ,i mean no location selected then it is giving me 41

what i want when user open this sheet it has to show current value 71 in the form of kpi

please help me...

i will try to send you sample qvw

[kunkumnaveen]

i had changed the date formate in guage chart

but its not giving me proper answer

[sfatoux72]

Try this:

Alt(FirstSortedValue({$<LOCATION = {'CHANDERIA'}>} OVERALL_SCR, -ADATE), 0)

[kunkumnaveen]

HI,

my default i need to show current score of all the location without any selection ,i think by writing your expression i am able to achieve it ,as soon as i open the sheet it showing the current value with out any selection.......

but the problem is  when user select other then CHANDERIA from locatiopn drop down this CHANDERIA GAUAGE CHART NEEDLE should show zero and wise versa for all ............

with ur expression i am able to achieve the first requirement but not the second one ..i think we should do a samll change in expression

[sfatoux72 · Accepted solution]

Ok, like that I think it will be ok:

Alt(FirstSortedValue({$<LOCATION *= {'CHANDERIA'}>} OVERALL_SCR, -ADATE), 0)