topic Re: System field $Field & Set Analysis & Alternate States in QlikView

System field $Field & Set Analysis & Alternate States

Wed, 09 Oct 2013 18:59:42 GMT

Hi,

So I have a unique problem. I want to give the user the ability to select two columns/field names from a listbox that contains the contents of $Field(the System field showing all of the column names). The user will select two different columns/field names. I will have a graph that will take these two seperate selections and create a graph showing both selections.

For example:

-Two field names I am using are ACDCalls and ABNDCalls

I was thinking that I will have two list boxes of $Field. Using Alternate States, I will place a different state on each of the boxes so that I can select two seperate fields from the same $Field. I will have a chart in the inherited state. I will have two expressions such as:

(sum( {State1}[$Field]))

(sum( {State2}[$Field]))

It does not work. The graph shows no data. However, when I change things up and use a different field instead of $Field, I am able to use alternate states and set analysis, so it is not my syntax that is incorrect. I am guessing that using a systemfield such as $Field with alternate State and Set Analysis will not work.

Could anyone give me a better option or fine-tune my current idea?

Re: System field $Field & Set Analysis & Alternate States

swuehl — Wed, 09 Oct 2013 19:24:53 GMT

Maybe like this

sum( $(=only({State1} $Field)) )

sum( $(=only({State2} $Field)) )

Re: System field $Field & Set Analysis & Alternate States

Wed, 09 Oct 2013 19:29:15 GMT

I had the same thought. My current(and exact formula) is

=if([$Field] = 'acdtime',Time(( (sum( $(=ONLY($Field))) ) )/(3600*24),'hh:mm:ss'),

I would try to place the alternate state in there...

=if([$Field] = 'acdtime',Time(( (sum({State1} $(=ONLY($Field))) ) )/(3600*24),'hh:mm:ss'),

I did try yours and it did not work either.

Re: System field $Field & Set Analysis & Alternate States

Wed, 09 Oct 2013 19:36:58 GMT

Update: I took out the if statement logic :

=Time(( (sum( $(=only({State1} $Field)) ) ) )/(3600*24),'hh:mm:ss')

It looks to be working. The selection is being read from an alternate state and utilizing the systemfield $Field. So now if I can plug in my if statement I'd be set to cascade this change to the rest of the formula.

Re: System field $Field & Set Analysis & Alternate States

Thu, 10 Oct 2013 17:38:35 GMT

Hi does anyone have a solution for this? It appears I cant use an if statement with Only() function, which is unfortunate (unless I am mistaken).

Re: System field $Field & Set Analysis & Alternate States

swuehl — Thu, 10 Oct 2013 17:45:39 GMT

A solution to what?

Sorry, I don't understand your current setting and requirement. Please post a small sample and a description of your expected result.

Re: System field $Field & Set Analysis & Alternate States

Thu, 10 Oct 2013 18:06:57 GMT

Example:

=if([$Field]='acdtime',Time(( (sum( $(=only({State1} $Field)) ) ) )/(3600*24),'hh:mm:ss'),

if([$Field] = 'acdcalls',num(sum(acdcalls),'####'))

I will have two list boxes that where the user will select from a list of system fields to compare two sets of data in one graph. Since the list of fields come from the same system field($Field), I have to break it up into Alternate States so that the user can select two values from two different list boxes. The problem I now face is that the user can potentially select a time value(acdtime which will be displayed in hh:mm:ss) and a number value.

I could leave the expression as :

Time(( (sum( $(=only({State1} $Field)) ) ) )/(3600*24),'hh:mm:ss'),

However, if the user selected his second value as a number and not a time value, the number would be shown in hh:mm:ss as well. I hope it is becoming clearer apologies!