Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
I think this should be easier:
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
@DyadyaFedor try below
=Count({<Month={'01'}, Revenue={">0"}>+<Month={'05'}, Revenue={">0"}>} distinct Strength&Diameter&length)
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
@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)
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?
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.