14 Replies Latest reply: Nov 21, 2014 7:49 AM by Malek Safa

Sum based on selected field

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,

• Re: Sum based on selected field

This is my formula, sorry. Still not working

• Re: Sum based on selected field

Add quotes around variable

• Re: Sum based on selected field

It will always display 0, regardless the selection

• Re: Sum based on selected field

can you upload a sample document ?

• Re: Re: Sum based on selected field

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,

• Re: Re: Re: Sum based on selected field

is it something like that?

• Re: Re: Re: Sum based on selected field

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

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

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

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

• Re: Sum based on selected field

Hi

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

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

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: Sum based on selected field

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)'}