Calculate/Expression from a specific table loaded into Qlikview

[samuel_lin]

Hello,

I have two tables, and I am using Email as the key field to connect the two. I'd like to make a bar chart to count Emails from Table2 only. But since these two tables are connected by "Email", I was not able to do it successfully.

Some description for Table2 Email field, The same email may occur 0, 1 or more than 1 times denoting frequency (whereas Email in table1 will only show up 1time), so somebody's email may show up 6 times meaning this person has showed up 6times.

There's another thing that I'd like to accomplish, from table2, I'd like to do a count of those whose email has show up for more than 10 times, between 5 to 10 times, 2 to 4 times and 1 time. Is this possible?

My apology that I may not have described my data table well, please ask me questions if you find my description/question unclear. Thank you so much for your help.

Sameul

[Anonymous]

Samuel,

You created field TestEmail as data island - not connect to the rest of the data model.  Hence, it didn't do it any good.  Nothing is wrong in using data islands, they have there usage, but not here.
The field Email links it to the other tables.  In my example, which you cannot open, I simply created additional field in the existing Table2 rather than in a separate table.  Logically it doesn't matter in your case.  But in general the fewer tables the better.

Next, nothing's changing on click because in this case each bar represents separate expression.  Typically it is not the case - there is one expression, and bars represent values of the chart dimension.  Clicking on bar selects value in the dimension.  In the "dimensionless" chart, like here,  there is nothing to select.
I can't tell if it is possible in your case.  Upload your application (only qvw), and tell me what you want to get selected on click.  I'll try when I have time.

[samuel_lin]

Michael,

Thank you for the explanation. It's superly appreciated~~

The attached is my Qlikview File, I have many tabs, the one that I am using to test is called "Testing."

(I created a new field called "Blank Dimension," and it's just "Blank Dimension" for all the cells in the column in Table2 hoping to use Blank Dimension as the dimension to make bars clickable, no luck)

Thank you so much for taking the time helping me to get a solution.

I am hoping for the good news soon~

Samuel

[Anonymous]

Samuel,

First, you can't use a field in your expressions if the field doesn't exist.  Expression
count({<ID={*}>} distinct Email)
was about my example which you couldn't open.  I mentioned that there was field ID in Table2.  You don't have it, so the expression is incorrect.  In your case is better to use simple
count(distinct EmailTest)
Because the field EmailTest is what you want to count, and it exists only in Table2.

Second, it is possible to make the bars "clickable".  Here is how:
Create calculated dimension

dual(
if(aggr(count([EmailTest]),Email)>10, '>10 Appt',
if(aggr(count([EmailTest]),Email)>=5, '5 to 10 Appt',
if(aggr(count([EmailTest]),Email)>=2, '2 to 4 Appt', '1 Appt')))
,
if(aggr(count([EmailTest]),Email)>10, 1,
if(aggr(count([EmailTest]),Email)>=5, 2,
if(aggr(count([EmailTest]),Email)>=2, 3, 4)))
)

In the sort tab, sort it by Numeric Value.
Remove all expressions and replace with one expression:
count(distinct EmailTest).

Now clicking on a bar selects Emails that are counted in this bar.

Additional recommendation - if you're going to keep dimension Program, check the box "suppress when value is null".

Hope I've answered all your questions this time...

Regards,
Michael
PS: Please don't upload large files.  You can always reduce data and create a smaller version.

[samuel_lin]

Michael,

sorry about the large file, I will try to attach a smaller size the next time.

I really do admire your understanding and skills over Qlikview coding, how should I train myself to be better? I should have mentioned that I am still a college student, I really enjoy working on Qlikview and this is my second time evaluating Qlikview for an organization.

I am feeling about right to close this close this case to become "answered." There's one more thing that I wanted to ask, how do I deselect Qlikview? I think I remember there's a way to do it. For what I wanted to accomplish in this thread, will I be able to have a bar for 0 time and do the count in the same way?

The fall semester is about to be over, these next two weeks are finals weeks. I am hoping to work on Qlikview some more in January, If I have development questions, may I forward my posts in Qlikview community to you?

Many thanks,

Samuel

[Anonymous · Accepted solution]

Samuel,

It possible to count Emails that don't have EmailTest associated with them.  Try to change chart's calculated dimension to this

=dual(

if(aggr(count([EmailTest]),Email)>10, '>10 Appt',

if(aggr(count([EmailTest]),Email)>=5, '5 to 10 Appt',

if(aggr(count([EmailTest]),Email)>=2, '2 to 4 Appt',

if(aggr(count([EmailTest]),Email)=1, '1 Appt',

if(aggr(count([EmailTest]),Email)=0, '0 Appt')))))

,

if(aggr(count([EmailTest]),Email)>10, 1,

if(aggr(count([EmailTest]),Email)>=5, 2,

if(aggr(count([EmailTest]),Email)>=2, 3,

if(aggr(count([EmailTest]),Email)=1, 4, 5))))

)

And, change the expression to this:

count(distinct Email)

I'm not sure you'll like it because 0's bar is way larger than others.  Anyway, for the sake of trying...

I'm sure you'll be back to QlikView, it is addictive .  You'll be better with time.  Go through the QV training materials, try exercises, maybe get a book.  The best training - try to answer questions on QlikCommunity.

I do not recommend forwarding posts to me (or to anyone in particular) directly.  There are hundreds of very good QV specialists here, many of them better than I, and they'll be happy to help.  And I am not always available.

Good like with your finals.

Regards,

Michael