topic Re: Sum based on selected field in QlikView

Sum based on selected field

Fri, 21 Nov 2014 11:29:52 GMT

Hi,

I'm trying to calculate a sum based on a selected value from a field.

This is my formula. What is wrong with it?

=sum({<Application = {$(vApplication)}[[Scheduled Loads])

Basically, based on what selected value is in the Application List Box, I want to calculate the Scheduled Loads.

Thanks,

Adrian

Re: Sum based on selected field

Fri, 21 Nov 2014 11:35:25 GMT

=sum({<Application={$(vApplication)}>}[Scheduled Loads])

This is my formula, sorry. Still not working

Re: Sum based on selected field

anbu1984 — Fri, 21 Nov 2014 11:40:53 GMT

Add quotes around variable

=sum({<Application={'$(vApplication)'}>}[Scheduled Loads])

Re: Sum based on selected field

maleksafa — Fri, 21 Nov 2014 11:41:37 GMT

if the variable vApplication contain only the application that you want to see then there is no need to add anything in your set analysis just sum([scheduled loads]) and it will take your selected application, now if you have another treatment for that you need to check if the vApplication is a string then you need to add quotes to your filter ={'$(vApplication)'}

Re: Sum based on selected field

Fri, 21 Nov 2014 11:43:39 GMT

It will always display 0, regardless the selection

Re: Sum based on selected field

maleksafa — Fri, 21 Nov 2014 11:46:29 GMT

can you upload a sample document ?

Re: Sum based on selected field

jonathandienst — Fri, 21 Nov 2014 11:49:06 GMT

If you are setting vApplication to whatever you have selected in the list box, then this is all redundant. You do not need the variable, and just use =sum([Scheduled Loads]) in your expression.

If you don't want this to calculate or display unless something is selected, you can use conditional display or conditional calculation to prevent display or calculation if nothing is selected, for example: =GetSelectedCount(Application) > 0

HTH

Jonathan

Re: Sum based on selected field

Fri, 21 Nov 2014 11:51:20 GMT

The problem here is that, if I have 2 selected values (out of 4 possible), I want two different numbers, not the overall sum

Re: Sum based on selected field

maleksafa — Fri, 21 Nov 2014 11:55:20 GMT

then you need to add the application filed as a dimension, this way if you select 2 applications you will get one number for each selection

Re: Re: Sum based on selected field

Fri, 21 Nov 2014 12:14:27 GMT

This is the document. Basically, if you see the bar chart, the numbers on the line are "hardcoded", but, what I want to do is whenever there is no value selected in Application, all 4 numbers should be there, but, when one, or two or three Applications are selected, I only want one, two or three number there, on the line graph.

Is that possible?

Thanks,

Adrian

Re: Re: Re: Sum based on selected field

maleksafa — Fri, 21 Nov 2014 12:31:57 GMT

is it something like that?

Re: Re: Re: Sum based on selected field

Fri, 21 Nov 2014 12:37:16 GMT

Close, but not quite what I want. The line should be horizontal. Basically, what you did with the numbers is perfect, however, it doesn't look how I need it. What I need is my "design" with your "implementation". How can that be done?

Re: Re: Re: Sum based on selected field

maleksafa — Fri, 21 Nov 2014 12:40:13 GMT

but if you make the line horizontal then you mean that all the values across this line are equal which is not the case in your situation as the line value represent the scheduled load for each application. so for each application you will get a value which means that the line will not be horizontal, unless you want a static value across all dimensions like the average of the schedule load for all the applications.

Re: Re: Re: Sum based on selected field

Fri, 21 Nov 2014 12:43:48 GMT

Exactly, I want the line to be horizontal, but I want different values on it, like a threshold for each application, that's why my graph adds the numbers "hardcoded" from the Presentation tab. This approach is good as well, but how can I make the numbers modify depending on my selection.

Re: Re: Re: Sum based on selected field

maleksafa — Fri, 21 Nov 2014 12:49:11 GMT

i don't think the option of horizontal line will work, now for the approach that i gave you, the numbers are changing if you select different applications, however with the date it is not working as your schema is not linked, you need to redesign your schema so that the tables are linked between Date field and [Week Ending] date