Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
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
Creator III
Creator III

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

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

Not applicable
Author

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
Creator III
Creator III

Hi Mike,

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

Warm Regards,

Karthikeyan A R.

Not applicable
Author

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
Creator III
Creator III

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
Author

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
Creator III
Creator III

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
Author

Karthikeyan,

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

Mike

karthikeyan1504
Creator III
Creator III

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))