topic Re: Variable in QlikView

Variable

shamitshah — Wed, 25 Nov 2020 16:16:04 GMT

Hi,

I have the following variable which gives me the count of items <>-1.

=Count (DISTINCT if(BST_IsInactive <> (-1),[Item Number] ))

I require another variable similar to the one above which gives me the count over the entire data set but should not change with any selections. This will allow me to do some % calculations based on the the total number.

Any ideas?

Thanks

Shamit

Re: Variable

sunny_talwar — Thu, 16 Jun 2016 01:14:01 GMT

Not sure I understand what you are trying to do Shamit. Can you elaborate please?

Re: Variable

trdandamudi — Thu, 16 Jun 2016 01:16:16 GMT

May be this:

=Count ({1} DISTINCT if(BST_IsInactive <> (-1),[Item Number] ))

Re: Variable

sunny_talwar — Thu, 16 Jun 2016 01:19:41 GMT

Do we need another {1}? Not sure, but may be this:

=Count ({1} DISTINCT if(Only({1} BST_IsInactive) <> (-1),[Item Number] ))

Re: Variable

shamitshah — Thu, 16 Jun 2016 02:16:26 GMT

Hi Sunny,

With the current variable below, I can get a total distinct count of the number of items in a text box.

=Count (DISTINCT if(BST_IsInactive <> (-1),[Item Number] ))

Assume that the variable gives a total of 1000 (Item A= 600 and Item B = 400)

Therefore, as a percentage of total number of items , A=60% and B=40%

If I pick item A from the drop down list the percentage changes to 100%, because the total number now changes to 600.

Irrespective of whether I pick A or B from the drop down list, I want the the total number to remain at 1000 to calculate the percentage of total number of items.

I tried both the suggestions below, but not getting any result in a text box.

Thanks

Shamit

Re: Variable

sunny_talwar — Thu, 16 Jun 2016 02:22:11 GMT

How about changing it to set analysis for percentage:

=Count (DISTINCT {<BST_IsInactive = {'<>1'}>} [Item Number])/Count(DISTINCT {1<BST_IsInactive = {'<>1'}>} [Item Number])

Re: Variable

trdandamudi — Thu, 16 Jun 2016 10:30:24 GMT

Because he is saying that "The total number should not change and he will be using for % calculations" may be this:

=Count(Distinct {<BST_IsInactive - = { '-1'} >} [Item Number] ) /

Count( {1} Total {<BST_IsInactive - = { '-1'} >} DISTINCT [Item Number] )

=Count(Distinct {<BST_IsInactive - = { '-1'} >} [Item Number] ) /

Count( {1} Total DISTINCT {<BST_IsInactive - = { '-1'} >} [Item Number] )

Re: Variable

shamitshah — Fri, 17 Jun 2016 01:12:14 GMT

Hi,

I now have a variable as follows:

=Count ({1}DISTINCT if(BST_IsInactive <> (-1),[Item Number] ))

This now forms part of the denominator for the calculation which remain static.

I have tested it by changing selections and it appears to be working.

Thanks

Shamit

Re: Variable

trdandamudi — Fri, 17 Jun 2016 11:16:15 GMT

I am glad that it worked out...