topic Selecting Expression fields as well as Dimensions Dynamic Chart in QlikView

Selecting Expression fields as well as Dimensions Dynamic Chart

Wed, 21 Dec 2011 22:32:38 GMT

I have been using macros for a short while now and am slowly getting the hang of it.

Recently I encountered a problem where I want my results to be filtered by both the dimension and the expression selected, the problem being is that the expression selected only results in a count and I would like for it to still display this count, but also narrow down the resulting set. (Clarification (hopefully) below)

This is only a sample of my data but here's what I am thinking.

Keep in mind these are in excel then imported into QlikView for external modification.

For my expressions table I have:

%ExpName %Expression %ExpressionSelect

Cancelled Counts Count(Distinct If(CLS_STATUS_CD_CC='CL',LEAD_ID_CC)) CLS_STATUS_CD_CC='CL'

In my macro I have:

set expValues = ActiveDocument.fields("%Expression").getPossibleValues

set expSelection = ActiveDocument.fields("%ExpName").GetSelectedValues

for i=0 to expValues.Count - 1

'msgbox("Expression Formula:" & expValues.Item(i).Text)

chart.AddExpression expValues.Item(i).Text

'rename an expression label

set p = chart.GetProperties

set exps = p.Expressions

set expItem = exps.Item(i).Item(0).Data.ExpressionVisual

expItem.Label.v = expSelection.Item(i).Text

chart.SetProperties p

This gets the label from %ExpName to display for the %Expression, however upon selecting this label I would like QlikView to also somehow implement the %ExpressionSelect or select 'CL' for the field CLS_STATUS_CD_CC. What changes would I need to make to accomplish this?

Sorry if this is convoluted, I'm doing my best to clarify.

Thank you in advance for any help.

Selecting Expression fields as well as Dimensions Dynamic Chart

pover — Wed, 21 Dec 2011 22:43:15 GMT

If I understand correctly, you want to make an automatic selection in the CLS_STATUS_CD_CC field based on your selection of the expression cancelled counts? Does each expression require a different selection to be made? Could there be conflicting selections? Is there a reason why you want to select it and not just label the expression as only counting cancelled counts?

Regards, Karl

Selecting Expression fields as well as Dimensions Dynamic Chart

Wed, 21 Dec 2011 23:02:03 GMT

Yes, thats what I'm hoping for. Each expression does require a different selection to be made. I was hoping that the selections would not conflict in the sense that you might simply add the next expressions result to the existing result that is to say:

I would hope that when one selected both Quoted and Cancelled Counts. It would show the counts for each and show the results for everything with a CLS_STATUS_CD_CC = 'QT' & CLS_STATUS_CD_CC = 'CL'

For the above lets assume another expression in my expressions table:

%ExpName %Expression %ExpressionSelect

Quoted Counts Count(Distinct If(CLS_STATUS_CD_CC='CL',LEAD_ID_CC)) CLS_STATUS_CD_CC='QT'

Selecting Expression fields as well as Dimensions Dynamic Chart

pover — Wed, 21 Dec 2011 23:13:45 GMT

This is probably possible in a macro with the "select" function, but it might be tricky. Instead of that can you link the expressions table to the data model?

Selecting Expression fields as well as Dimensions Dynamic Chart

Thu, 22 Dec 2011 18:34:28 GMT

I'm trying the trickier version, lol. I looked up the select syntax so I had to add an additional column to my excel spreadsheet.

Now I have added %ExpressionSelection and changed %ExpressionSelect to look like this

%ExpressionSelect %ExpressionSelection

CLS_STATUS_CD_CC CL

CLS_STATUS_CD_CC CT

etc...

So now for my macro I have added:

set expValues1 = ActiveDocument.fields("%ExpressionSelect").getPossibleValues

set expSelection1 = ActiveDocument.fields("%ExpressionSelection").getPossibleValues

msgbox("Expression Formula:" & expValues1.Item(i).Text)

msgbox("Expression =:" & expSelection1.Item(i).Text)

ActiveDocument.GetField("expValues1.Item(i).Text").Select "expSelection1.Item(i).Text"

I have tried using Fields rather than GetField, removing quotes around the expValues1.Item(i).Text and the expSelection1.Item(i).Text. And still this doesn't work.

The strange thing is that the msgboxes are showing up correctly. They display CLS_STATUS_CD_CC, then CL.

Then the code jumps to the Macro and highlights the ActiveDocument.GetField line

and says Object required: 'ActiveDocument.GetField(...)' at the top

Any thoughts?

Thank you again!

Selecting Expression fields as well as Dimensions Dynamic Chart

Thu, 22 Dec 2011 21:24:31 GMT

I actually was able to figure out a workaround but unfortunately this resulted in another problem.

The reason it wasn't working in case anyone looks at this later was that there was a space in one of the strings.

I simply defined at the top of the macro

dim selection

dim field

then later in my macro

field = trim(expValues1.Item(i).Text)

selection = trim (expSelection1.Item(i).Text)

So it works for one selection at a time but always breaks with any more than one selection.

Selecting Expression fields as well as Dimensions Dynamic Chart

Fri, 24 Feb 2012 17:35:00 GMT

For anyone who might be curious on how I was able to finally implement this, I realized I didn't need to use a macro...Instead I solved this using variables.

I began by defining Expr =Concat(%ExpName,'|'). This way whenever a selection was made, it would concatenate a large string of each expression selected.

Then i made individual variables defined in the following fashion:

vCancelledCnt=If(SubStringCount(Expr,'Cancelled Counts')=1,1,0)

etc...

From here you simply add the definition to each expression to your table(Expressions tab):

=If(vCancelledCnt=1,Count(DISTINCT If(CLS_STATUS_CD_CC='CL',LEAD_ID_CC)))

etc...

And then add a conditional load (Presentation tab):

Click on Cancelled Counts -> Conditional and defined it as vCancelledCnt=1.

etc...

Now your chart will only show values based on the expressions you've selected dynamically.

Selecting Expression fields as well as Dimensions Dynamic Chart

Fri, 16 Mar 2012 06:57:02 GMT

Mind sharing an example showing this?

Would be really helpful!

Re: Selecting Expression fields as well as Dimensions Dynamic Chart

Thu, 17 Jan 2013 11:35:32 GMT

@bapperson

Can u share a demo application for the above solution.

Thanks n Regards

Sumit thakur