Selecting Expression fields as well as Dimensions Dynamic Chart

[#1]

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

   next

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.

[pover]

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

[#3]

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'

[pover]

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?

[#5]

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!

[#6]

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.

[#7 · Accepted solution]

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.

[#8]

Mind sharing an example showing this?

Would be really helpful!

[#9]

@bapperson

Can u share a demo application for the above solution.

Thanks n Regards

Sumit thakur