Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Not applicable

Ignore the dimension, but uses it value in a new lookup?

I am recreating a report from another system in Qlik Sense. This report is a simple table of values for users where they are the event agent. So my dimension on the table is EventAgentName. The problem I am running into is that one of the columns in the report is counting where the user is the call center agent, not the event agent. Is there a way to ignore the dimension value for the sum, but get the dimension value to use in the new criteria?

9 Replies
karthikeyan1504
Contributor III

Re: Ignore the dimension, but uses it value in a new lookup?

I hope a if condition in ur expression will solve the problem,

count(if(dimension='Event Agent', EventAgentName)

Not applicable

Re: Ignore the dimension, but uses it value in a new lookup?

The Dimension of my table is the EventAgentName. For this column, I need to ignore that dimension and calculate over all the data. I tried using a Set Expression, but that doesn't seem to remove the table dimension. Plus, I need to get the value of the current rows dimension, so that I can use it in this query.

karthikeyan1504
Contributor III

Re: Ignore the dimension, but uses it value in a new lookup?

Hi Mike,

Could you please upload your qvw... so that it will be clear to understand.

Warm Regards,

Karthikeyan A R.

Not applicable

Re: Ignore the dimension, but uses it value in a new lookup?

There is a lot of business data in it, so not sure if I can share it.

Our model is like this:

Calendar

  • CalendarDate
  • PeriodNumber
  • ...

Events

  • CalendarDate
  • EventType
  • AppointmentId
  • LeadSourceId
  • SourceId
  • ...

Appointments

  • AppointmentId
  • CallCenterRepName
  • ...

LeadSources

  • LeadSourceId
  • EventAgentName
  • ...

On my sheet is a Filter Pane with CalendarDate and a Table.

The first column in the table is EventAgentName field (This is the Dimension for the table)

The other columns in the table are expressions. Some example of the expressions are:

  • COUNT(DISTINCT IF(EventType = 'AppointmentSet' AND IsRescheduleNowSetAppointment = 0 AND SourceName = 'Proximity Marketing' AND ISNULL(EventAgentName) = 0, AppointmentId))
  • COUNT(DISTINCT IF(EventType = 'AppointmentSet' AND AppointmentStatus = 'Deleted' AND IsRescheduleNowDeletedAppointment = 0 AND SourceName = 'Proximity Marketing', AppointmentId))

I need to add a column that uses an expression like this, but ignore the tables Dimension filter and uses tables Dimension value for the row:

COUNT(DISTINCT IF(EventType = 'AppointmentSet' AND IsNull(AppointmentResult) = 0 AND SourceName = 'Proximity Marketing' AND CallCenterAgentName = CurrentDimensionValue, AppointmentId))

Does that make sense?

karthikeyan1504
Contributor III

Re: Ignore the dimension, but uses it value in a new lookup?

Can you try creating the variable for CurrentDimensionValue as below?


In variable overview please add the variable CurrentDimensionValue = getcurrentfield(Dimension)


COUNT(DISTINCT IF(EventType = 'AppointmentSet' AND IsNull(AppointmentResult) = 0 AND SourceName = 'Proximity Marketing' AND CallCenterAgentName = $(CurrentDimensionValue), AppointmentId))


I have understood in this way. If this is not what you are expecting please let me know


Warm Regards,

Karthikeyan.

Not applicable

Re: Ignore the dimension, but uses it value in a new lookup?

Karthikeyan,


That didn't work, the column is empty. According to the Qlik Sense documentation, that function isn't supported in Qlik Sense. (https://help.qlik.com/sense/2.0/en-US/online/#../Subsystems/Hub/Content/Scripting/QlikView-functions...)


Mike

karthikeyan1504
Contributor III

Re: Ignore the dimension, but uses it value in a new lookup?

Hi Mike,

I was not aware of this... if you want to select only one dimension value at once, then you can use only() function.

CurrentDimensionValue = only(Dimension)


Warm Regards,

Karthikeyan.

Not applicable

Re: Ignore the dimension, but uses it value in a new lookup?

Karthikeyan,

Where does this "Dimension" value that you reference come from? Is it a variable that is populated by Qlik?

Mike

karthikeyan1504
Contributor III

Re: Ignore the dimension, but uses it value in a new lookup?

It should be your dimension field name..

Lets take the filed name as salesperson, so the formula would be,

COUNT(DISTINCT IF(EventType = 'AppointmentSet' AND IsNull(AppointmentResult) = 0 AND SourceName = 'Proximity Marketing' AND CallCenterAgentName = only(salesperson), AppointmentId))

Community Browser