count number of rows in a table with conditions

[DyadyaFedor]

Hello.

I have a table with three columns as dimension and two columns as measure.

strength, diameter, length, revenue01  and revenue05 (which is same revenue, but with a modifier)

In the table title, I have a formula

='The table contains '&count(distinct(Strength&Diameter&length))&' strength-diameter-length combinations', which calculates the total number of rows in the table.

Additionally, I would like to calculate the number of rows in the table, where revenue01 AND revenue05 are greater than 0.

I've managed to count the number of rows separately for month 1 and month 5,

=Count({<Month={'01'}, Revenue={">0"}>} DISTINCT Strength&Diameter&length)

But how do I add AND condtion?

Thank you

[Clever_Anjos]

I think this should be easier:

1. Create a field in your script with hash256(strength, diameter, length) as _counter_
2. Use count({<Month={'01','05'}, Revenue={">0"}>} distinct _counter_) 

[DyadyaFedor]

thank you.

I've tried to insert  hash256(strength, diameter, length) as _counter_ into my load script, but didn't succeed. I suspect it's because these fields aren't actually part of the data model. I create them in the load script myself.

and if I use count({<Month={'01','05'}, Revenue={">0"}>} distinct strength&diameter&length) inside my app, I get the total number of rows in the table, so it looks like Qlik understands this condition as OR. not as AND

[Kushal_Chawda]

@DyadyaFedor  try below

=Count({<Month={'01'}, Revenue={">0"}>+<Month={'05'}, Revenue={">0"}>} distinct Strength&Diameter&length)

[DyadyaFedor]

thank you.

I've tried this one as well. I am getting the total number of rows again., as if + meant an OR condition, not an AND

[Kushal_Chawda]

@DyadyaFedor  Not sure but you can try as well below.. If it doe not work the share sample appliation

=Count({<Month={'01'}, Revenue={">0"}>*<Month={'05'}, Revenue={">0"}>} distinct Strength&Diameter&length)

[DyadyaFedor]

The result of this formula is 0, as if there are no lines where revenue in both months is positive. How do I share a sample application with you?

[DyadyaFedor]

In the meanwhile, inside the table I added two columns:

1. =rowno(total) it calculates me the total number of rows

2. if(sum({<Month={'01'}>}revenue>'0' and sum({<Month={'05'}>}revenue>'0',1,'0')

theoretically I can could even divide one value by another and say something like out of 21 products that we sold either in month 1 or in month 5, 15 products were sold in both months.