Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi everyone,
I have a problem that has been plaguing me for a few hours and I can't figure it out.
I have a table like this:
| ProductId | int1 | int 2 | int3 |
I created a variable where, depending on a text box created on my app, it takes on certain values. These values are the ones I have to search for in the int 1, int 2 and int 3 fields.
The problem is as follows:
| Product1 | 111 | 222 | 333 |
If my variable (vint_n) takes the values of "111", "222" (i.e. a list of values)
the product1 i will be counted 2 times. And that's not good...
The formula I use is as follows:
count({<Year={'$(=max(Year)-1)'} , int1={$(vint_n)}>}progressivo)
+
count({<Year={'$(=max(Year)-1)'}, int2={$(vint_n)}>}progressivo)
+
count({<Year={'$(=max(Year)-1)'}, int3={$(vint_n)}>}progressivo)
How can I change the formula to give me the expected result?
thank you all!
Hi Alexander,
I love "complicated" - that's why I am always starting my lecture on Set Analysis with Albert Einstein's quote "Everything should be made as simple as possible, but not simpler".
I'd calculate it like this (if I understood your complexity correctly):
count(
{<Year={'$(=max(Year)-1)'} }>}
progressivo * (
SubstringCount('$(vint_n)', int1)+
SubstringCount('$(vint_n)', int2)+
SubstringCount('$(vint_n)', int3)
)
)
However, keep in mind that the function Count() will count zeroes as well, so in case of no matches, you will still get a count of 1. Maybe you can find a way to use sum() rather than count - that depends on the structure of your data.
To learn more advanced development techniques, check out the agenda of the Masters Summit for Qlik - coming soon to Orlando and to Dublin!
thanks for reply @Oleg_Troyansky but your formula give me a "error in set modifier expression".
Can you help me?
There is an extra closing bracket there - I copied your formula and removed one filter, but the bracket got overlooked. This should be ok:
count(
{<Year={'$(=max(Year)-1)'}>}
progressivo * (
SubstringCount('$(vint_n)', int1)+
SubstringCount('$(vint_n)', int2)+
SubstringCount('$(vint_n)', int3)
)
)
i'm sorry @Oleg_Troyansky but there is another error: ')' expected
Why don't you take over at this point, my friend? I typed these formulas here on the forum, with no color coding and no syntax check. I gave you a solution to you problem. Kindly take care of the parentheses and the brackets on your own, OK?