topic Re: expression going wrong in kpi in QlikView

expression going wrong in kpi

kunkumnaveen — Mon, 14 Mar 2016 10:17:50 GMT

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

Re: expression going wrong in kpi

kunkumnaveen — Mon, 14 Mar 2016 10:51:19 GMT

can any one help me with the above requirement

please

Re: expression going wrong in kpi

mightyqlikers — Mon, 14 Mar 2016 10:53:23 GMT

hi,

please share sample qvw.

Re: expression going wrong in kpi

Digvijay_Singh — Mon, 14 Mar 2016 11:20:37 GMT

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.

Re: expression going wrong in kpi

Anonymous — Mon, 14 Mar 2016 11:25:03 GMT

plz share the sample data.....

Re: expression going wrong in kpi

kunkumnaveen — Mon, 14 Mar 2016 11:44:42 GMT

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

Re: expression going wrong in kpi

kunkumnaveen — Mon, 14 Mar 2016 12:07:08 GMT

i had changed the date formate in guage chart

but its not giving me proper answer

Re: expression going wrong in kpi

sfatoux72 — Mon, 14 Mar 2016 12:21:58 GMT

Try this:

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

Re: expression going wrong in kpi

kunkumnaveen — Mon, 14 Mar 2016 12:32:43 GMT

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

Re: expression going wrong in kpi

sfatoux72 — Mon, 14 Mar 2016 12:48:00 GMT

Ok, like that I think it will be ok:

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

Re: expression going wrong in kpi

kunkumnaveen — Mon, 14 Mar 2016 13:04:58 GMT

HI,

Thanks for you ur help ,it worked .......appreciated,

i am sorry for annoying you ,i need a last help from you

i am trying to display current and previous scores for each location in a bar chart which i have done,

expression :firstsortvalue(overall_src,-ADATE)(CURRENT )

firstsortvalue(overall_src,-ADATE,2) (PREVIOUS)

But my client also need respective quarter with those scores,i thought it will be easy because i got quarter field ..and i added it as second dimension ....

after adding that it is showing all the quarters for all location...

what i need is only current and pervious quarters for all location

Re: expression going wrong in kpi

sfatoux72 — Mon, 14 Mar 2016 13:34:48 GMT

It's perhaps better to create a new question.

You will have more answer and It will be better for other people with same issue to find it.

Re: expression going wrong in kpi

kunkumnaveen — Mon, 14 Mar 2016 13:36:38 GMT

yes i have done that