display just a calculated total in a text box from a table

chrismtb — Wed, 01 Jun 2016 16:00:14 GMT

Hi,

I have a data set currently set out similar to the below example (but with a lot more depth to the information). I have a number of dashboards which query the data in different ways and provide a number of views.

One of the views i had required me to pull out where i had duplicated Ref ID's but only in the cases where the status was valid (in this case all valid statuses contain " - ".

Sys ID	Ref ID	Status	Name	Description
1	12345	1 - plan	project a	project info
2	54321	2 - build	project b	project info
3	11112	2 - build	project c	project info
4	12457	rejected reason a	project d	project info
5		4 - concluded	project e	project info
6	12345	2 - build	project f	project info
7	12345	funding denied	project g	project info

I used the below logic to come up with a simple table view that works:

=IF(wildmatch([Status],'*-*'),IF([Ref ID]>0,IF(count([Ref ID])>1,count([Ref ID]),),))

which allows me to exclude both where there is not a valid status and where there are blank Ref IDs... IE using the example table i would expect to return results with a total at the bottom:

Ref ID	COUNT
12345	2
	2

(this result is Sys ID 1 and 6)

As previously mentioned i added a number of other views to look at other requirements and decided the best way to look at things was a high level summary tab.

For this i have a text box with the condition for each table and then another text box with the total for that condition.

I am stuck though because i want to display the total from the results table (directly above) in this text box.

I have tried using another count/sum statement around my formula to get the result i.e.

=sum(IF(wildmatch([Status],'*-*'),IF([Ref ID]>0,IF(count([Ref ID])>1,count([Ref ID]),),)))

but this just does not work plain and simple.... can anyone give me some direction as how to solve this (i suspect it involves set analysis of some sort but i have quite a bit going on in the formula and can't get it clear in my head!)

Re: display just a calculated total in a text box from a table

sunny_talwar — Wed, 01 Jun 2016 16:52:21 GMT

Try this:

Count(Aggr(If(WildMatch([Status],'*-*'), If([Ref ID]>0, If(Count([Ref ID]) > 1, Count([Ref ID])))), [Ref ID]))

Sum(Aggr(If(WildMatch([Status],'*-*'), If([Ref ID]>0, If(Count([Ref ID]) > 1, Count([Ref ID])))), [Ref ID]))

Re: display just a calculated total in a text box from a table

chrismtb — Thu, 02 Jun 2016 11:31:24 GMT

I used the second version (the sum) and it works perfectly - Thanks Sunny T

topic Re: display just a calculated total in a text box from a table in QlikView

display just a calculated total in a text box from a table

Re: display just a calculated total in a text box from a table

Re: display just a calculated total in a text box from a table