topic Re: Expression in QlikView

Expression

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

Hi All

In the attachment i have 2 charts One is bar graph and the other is Block chart.

I have derived.

If u look at the bar chart the dimesnions (Sensitivity, resistant,Internediate ect) are derived from Drugresult .

Required :

If i clik any one content say Sensitivity whose output count is =790,

And accordingly the Block charts show only for sensitvity , but the numbers dont match.

Can any one suggest me that what Expression do in need to add under block char so that the count of both the charts remain same.

Thanks

Re: Expression

kaushiknsolanki — Tue, 15 Mar 2016 10:54:19 GMT

Hi,

The reason is one output has multiple DRUGRESULT and thus they are getting counted more then 1 where as your Bar chart shows a distinct against the DRGRSLT.

Regards,

Kaushik Solanki

Re: Expression

sunny_talwar — Tue, 15 Mar 2016 10:56:19 GMT

I think the issue is related to the distinct count. In your bar chart it is showing the distinct count by DRGRSLT which after selection is just for one value. Whereas the block chart still has multiple dimensions and the count is distinct for each value within your dimension. The problem is that two dimensions can count a distinct value once for each of value. So if you sum up all the count, you will end up having more than the actual distinct count.

Re: Expression

MK_QSL — Tue, 15 Mar 2016 10:58:33 GMT

Change expressions as below

=SUM(Aggr(Count(Distinct output),DRUGRESULT))

Re: Expression

smilingjohn — Tue, 15 Mar 2016 11:08:18 GMT

Hi Manish Thanks for your reply

It shoult be count only , i tried with sum it works perfectly , whatever the dimension i select accordindly the values are same in the both charts .

But when i replace sum with count the values are very less .

Can you please look into this

Re: Expression

smilingjohn — Tue, 15 Mar 2016 11:10:21 GMT

Hi Sunny

If you look the sript i have merged in that way , wherever ther is "SN " it should be Sensitivty and so on ,

I think you understood what i mean to say . for both the charts the value should match for count , It cannot be sum

Re: Expression

MK_QSL — Tue, 15 Mar 2016 11:22:12 GMT

If you simply use COUNT(Distinct output).. it will not work because you have more than one DRUGRESULT for single output.

Consider 8/1/2015|BSGA|1595953... For this output you have 6 Different DRUGRESULT.

So if you select this output, you will get 6 different blocks in block chart which will give you count 6 but

your bar chart would give you 1.

Re: Expression

smilingjohn — Tue, 15 Mar 2016 11:31:33 GMT

Yes i do agree

And you formula works other way that is

Under block chart i used this formula = 'Sum(Aggr(Count(Distinct output),DRGRSLT))' which gives the me the exact count of each individual as that in the bar charts but the problem is ,it is not showing all the blocks for sensitivity .

If i get that then iam good to go .

I hope u can provide me with some solution

Re: Expression

MK_QSL — Tue, 15 Mar 2016 11:34:43 GMT

What do you mean by now showing all the blocks for sensitivity? Please clarify by an example.

You don't have to change the expression for Block Chart.. Only use the expression suggested for Bar Chart.

Re: Expression

smilingjohn — Tue, 15 Mar 2016 11:42:58 GMT

Ok i understand , But iam trying to match the values of block chart with bar chart .

In bar chart if i select Sensitivty , then it shows only one block , rather it should show multiple blocks for sensitivyt and so on .

Thanks

Re: Expression

MK_QSL — Tue, 15 Mar 2016 11:45:00 GMT

Then Replace dimension

Use DRGRSLT Instead of DRUGRESULT in Block Chart

Re: Expression

MK_QSL — Tue, 15 Mar 2016 11:48:03 GMT

Or you can use something like this...

Re: Expression

smilingjohn — Tue, 15 Mar 2016 12:43:11 GMT

Thanks Manish

Actaully

In the Block chart i am using this exprssion "Sum(Aggr(Count( distinct %IncidentKey),DRGRSLT))"

And when i select only Sensitivty in bar chart in the block chart i see only one block that is

Which is matching the bar values

2. When i select Sensitvity i should get like this with tha same value on bar Sensitvity

Thanks

Re: Expression

MK_QSL — Tue, 15 Mar 2016 12:48:20 GMT

Use below expression in Bar Chart

Sum(Aggr(Count( distinct %IncidentKey),DRGRSLT))

and below expression in Block Chart

Count( distinct %IncidentKey)

Re: Expression

smilingjohn — Tue, 15 Mar 2016 13:00:20 GMT

Hi Manish ,

If i go with above expression then yes i get the desired result in bloack chart but the couts in block chart differs compared to Barchart .I think we are very near to achieve

Re: Expression

MK_QSL — Tue, 15 Mar 2016 13:09:57 GMT

Can you upload the latest file please... Also include a text box showing what result you are looking for if you select Sensitivty ..

Re: Expression

smilingjohn — Tue, 15 Mar 2016 13:15:38 GMT

Hi Manish

If i select Sensitivity in bar chart the block chart should show me the break of sensitvity with exact value that of bar chart selected Sensitvity . below is the napshot how it should appear

Re: Expression

smilingjohn — Tue, 15 Mar 2016 13:21:16 GMT

PLz find the attachment .

Now if you look at both the charts the values are matching this is what i want .

And when i select Sensitivty in Bar chart then only one block is seen in the Block chart with accurate value , where as it has to show multiple blocks as whon in the snapshot (provided when i sum up each block the value has to match with the sensitvity bar)

Re: Expression

MK_QSL — Tue, 15 Mar 2016 13:32:55 GMT

When you select Sensitivity, what value are are looking for in Bar Chart?

If it is 790, then it is wrong..

Re: Expression

smilingjohn — Tue, 15 Mar 2016 13:37:49 GMT

Hi Manish

That is correct , that is how we count the distinct .And accordingly it needs to be displayed in the Block chart which is also displying , the only problem in the block chart is it is showing only one block with count of 790 , wher as it needs to be displayed in many blocks