Skip to main content

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

cancel
Showing results for 
Search instead for 
Did you mean: 
wsb
Contributor
Contributor

Show values in data field found one month, but not the other

Hello,

I have a project where I am trying to show values that are in one month, but not the other. All of my data is in one table. The example is below.

CustomerName, Month, Total Sales

Tim, January, 20

Sarah, January, 15

Nick, January, 30

Joanne, January, 40

Tim, February, 10

Nick, February, 15

Meghan, February, 20

Fred, February, 5

 

In my document I have two multiboxes where you can pick any month, and one chart which uses the months chosen as a filter for ranges.

The problem I'm having is creating a chart which allows you to see only the customer's names (and data) that are in one month, but not the other.

In the data above, if I chose the values January and February, the chart would show:

Sarah, January, 15

Joanne, January, 40

Meghan, February, 20

Fred, February, 5

 

Thank you for your help!

1 Solution

Accepted Solutions
jwjackso
Specialist III
Specialist III

I use the P() and E() functions for these types of problems.  For demonstration purposes, I hardcoded the months into my formula.  I did an intersection of the P() function for customers in 2 different months and used the E() function to eliminate those customers.  In Qlikview I would suppress zeros and hide the column.

=Count({<CustomerName=E({<CustomerName=P({<Month={'January'}>}CustomerName)*P({<Month={'February'}>}CustomerName)>}CustomerName)>}CustomerName)

Capture.PNG

View solution in original post

2 Replies
jwjackso
Specialist III
Specialist III

I use the P() and E() functions for these types of problems.  For demonstration purposes, I hardcoded the months into my formula.  I did an intersection of the P() function for customers in 2 different months and used the E() function to eliminate those customers.  In Qlikview I would suppress zeros and hide the column.

=Count({<CustomerName=E({<CustomerName=P({<Month={'January'}>}CustomerName)*P({<Month={'February'}>}CustomerName)>}CustomerName)>}CustomerName)

Capture.PNG

wsb
Contributor
Contributor
Author

This works perfectly! Thank you!