topic Re: Help needed on expression in QlikView

Help needed on expression

Tue, 30 Sep 2014 13:47:07 GMT

Hi All,

Need 2 expressions as below.

I have to store values in my variable.

vStatus = ('Status', New Status', 'Old Status', 'Future Status')

2. then I have to get total count of the field where above values are in.

=TextCount(IF(Field = vStatus))

Can anyone give suggestion on this?

Thanks.

Re: Help needed on expression

JonnyPoole — Tue, 30 Sep 2014 14:34:45 GMT

vStatus: concat( distinct [Status] , ',') -> will give you a comma delimitted list of all the distinct values in the field [Status]

substringcount( $(vStatus) , ',') + 1 -> will count the number of commas in the first string (and add 1)

Re: Help needed on expression

Tue, 30 Sep 2014 15:29:51 GMT

Hi Jonathan,

My case may not be all the time word has "Status".

vStatus = ('Deleted', 'Ignored','Status', New Status', 'Old Status', 'Future Status')

I am not sure wheather it is possible or not, I want to keep all those words in variable and want to compare. Because those words may be increase/decrease so I just change variable values infuture instead of changing expression in the dashboard.

1. Needs to keep all the words/values here.

2. compare with variable. If any word existed then count as 1.

Thanks.

Re: Help needed on expression

JonnyPoole — Tue, 30 Sep 2014 15:41:54 GMT

That is OK, when I wrote [Status] i meant 'Status' as a field name , not a field value. If your field was called 'Call Status'

If you want the list to never change and just have all possible values just add a {1} to the concat() expression:

vStatus: concat( {1} distinct [Status] , ',') -> this evaluates the formula in the global context regardless of user selections, it will always have every possible Status no matter what the user selects.

Is that what you want ?

Re: Help needed on expression

JonnyPoole — Tue, 30 Sep 2014 15:56:53 GMT

for # 2 , you would just use this to see if the string contained in the variable 'vOtherVariable' is in the list of delimitted values in 'vStatus':

substringcount( $(vStatus) , $(vOtherVariable) )

Only concern is if the vOtherVariable contains a word that could exist in multiple places in vStatus... like the value 'South' existing in both of the following: 'South','South America' ... but you only want it to hit on 'South' and not 'South America'.

To account for that an additional twist is needed but let me know if that is needed for your use case.

Re: Re: Help needed on expression

Tue, 30 Sep 2014 16:35:57 GMT

Hi Jonathan,

I understand your solution. But my case is different, Kindly refer to the attached QVW.

If in future under field (F3), If I get more values (e.g. Status 4, Status 5 etc...), how can I show?

Thanks.

Re: Re: Re: Help needed on expression

JonnyPoole — Tue, 30 Sep 2014 16:44:20 GMT

Wow. This is completely different requirement. Thank you for posting your QVW / screenshot.

I altered your technique to make all test and status dynamic so the whole chart is data driven with no manual intervention needed when new status arrive.

My only question is that you had 'values on data point' before and it was only partially working. What is the requirement there ( if this is good) ?

Re: Re: Help needed on expression

Tue, 30 Sep 2014 17:15:11 GMT

Hi Jonathan,

The % I needs to show for only some of the Status only.

Thanks for your help. Let me implement into actual requirement and let you know.

Once again thanks for your time.