Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
alexandernatale
Creator II
Creator II

complicated: distinct count, or, and set analysis

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!

Labels (4)
5 Replies
Oleg_Troyansky
Partner Ambassador/MVP
Partner Ambassador/MVP

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
Creator II
Creator II
Author

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


Can you help me?

Oleg_Troyansky
Partner Ambassador/MVP
Partner Ambassador/MVP

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
Creator II
Creator II
Author

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

Oleg_Troyansky
Partner Ambassador/MVP
Partner Ambassador/MVP

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?