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

Newbie need help on expressions with count and if PLEASE!!

I been trying using the following expression to get the exactly amount of machines per year, but instead of that i just get the total of all years:

First I try this:

The values per year are: 2013=349, 2012=349, 2011=300, 2010=348.

Count

(if(DATE_MAQUINAS='2013',[DATE_MAQUINAS],

if(DATE_MAQUINAS='2012',[DATE_MAQUINAS],

if(DATE_MAQUINAS='2011',[DATE_MAQUINAS],

if(DATE_MAQUINAS='2010',[DATE_MAQUINAS])))))

The return is the total

Then I try:

Count

(if([DATE_MAQUINAS]=2013,[DATE_MAQUINAS]))

The result for the first two years it's correct but when I select on the List Box (Year) 2011 or 2010 shows wrong information.

The funny thing is that if put 2010 instead of 2013 the value it's right for that year but wrong for the other years when I select them on the List Box (Year), same for any year changed on the expression.

Count

(if([DATE_MAQUINAS]=2010,[DATE_MAQUINAS]))

Other thing that I need is to remove from the count some machines that are in colum ID_MACHINE, the ones that ends with Letter "A" and I don't have any idea how to do that.

I hope someone can help me with this.

THANKS

1 Solution

Accepted Solutions
Anonymous
Not applicable
Author

This should work:

count(distinct if(DATE_MAQUINAS>=2010 and DATE_MAQUINAS<=2013

     and wildmatch(DATE_MAQUINAS, '*A')=0

     , DATE_MAQUINAS))

Regards,

Michael

View solution in original post

10 Replies
somenathroy
Creator III
Creator III

Hi,

I think you need to include DATE_MAQUINAS column in dimention list in your chart. In that case you will get year wise count, so you need not to specify year in expression.Use the expression as below:

Count(if(not Upper(Trim(ID_MACHINE)) = 'A',[DATE_MAQUINAS]))

Regards,

Som

Not applicable
Author

No that didn't work.

I need two things:

1.- Count the correct value per year.

2.- Remove from that count the number that ends with letter "A".

As explain before I put the expresion:

Count (if([DATE_MAQUINAS]=2010,[DATE_MAQUINAS]))

That give me the correct value for 2010 that includes de Machine numbers with "A". It's equal to 318.

If I click on the list box (Year) for example 2012 the value showed on the Machines online it's nos correct. But if I change on the expresion from 2010 to 2013 shows the correct value 349. But click on the List Box (year) for example 2010 shows 225 and not 318.

The chart to show the values is a single comun as I show in the image.Sprite 17.jpg

Any idea how to fix the two things?

THANKS

Anonymous
Not applicable
Author

First, it is almost always neccessary to use "distinct" with count.
Next, it can be written simpler:

count(distinct if(DATE_MAQUINAS>=2010 and DATE_MAQUINAS<=2013, DATE_MAQUINAS))

Not applicable
Author

Thanks Michael

I try that to but it's not giving the correct info.

I change a little bit the expression because the colum DATE_MAQUINAS repeats the year on all the maquinas installed on that year:

count

(DISTINCT if(DATE_MAQUINAS>=2010 and DATE_MAQUINAS<=2013, MAC_NUMBER))

Now the numbers showed on 2013 and 2011 are correct when I click on List Box (year) but on the other ones still the same problem. Non acurate info.

And how can remove the MAC_NUMBER machines that ends with letter "A" from that count?.

THANKS FOR YOUR HELP

Not applicable
Author

Sorry Michael it's working!!! I forget to do something.

Now the issue is how to remove the Machines with the "A". And if there is no selection on the List Box (Year) shows the 2013 value on that field.

THANKS

Not applicable
Author

Any Ideas?

Anonymous
Not applicable
Author

This should work:

count(distinct if(DATE_MAQUINAS>=2010 and DATE_MAQUINAS<=2013

     and wildmatch(DATE_MAQUINAS, '*A')=0

     , DATE_MAQUINAS))

Regards,

Michael

Not applicable
Author

Michael Thanks a lot works perfect.

You make my day 😉

Regards

Not applicable
Author

Michael where I can find information of the programming expresions for qlikview and for newbies jejeje.

thanks