complicated: distinct count, or, and set analysis

[alexandernatale]

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!

[Oleg_Troyansky]

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!

[alexandernatale]

thanks for reply @Oleg_Troyansky  but your formula give me a "error in set modifier expression".

Can you help me?

[Oleg_Troyansky]

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)

     )

)

[alexandernatale]

i'm sorry @Oleg_Troyansky  but there is another error: ')' expected

[Oleg_Troyansky]

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?