Choose a value based on a deselected field

[#1]

Hi,

I'm trying to make a chart where the user chooses a name and what is shown in the chart is not the data for the single person that is chosen but the total for the group that the name correlates to.

This is a very simplified example of the table:

Name Group Sales

Daniel A 10

Sara A 15

Peter B 20

This is the expression as it looks now:

Count ({$<Name=, Group={'A'}>} Sales)

Instead of 'A' i want it to dynamically choose the correct group.

Help with this will be much appreciated!

[IAMDV]

Daniel,

Use the variable to refer the group and use that variable in your expression.

Good luck.

Cheers - DV

[IAMDV]

Daniel,

Please use this expression...

=SUM ({$<Name, Group={$(=Only(Group))}>} Sales)

You can use Sum or Count.

Cheers - DV

[#4]

Thank you for your fast reply, unfortunately this only gives me a - in the chart.

Any ideas?

[IAMDV]

Hi Daniel,

I am not sure if I had understood your question correctly. Let me know confirm with you...

With your sample data, if I select Daniel you should have 2 records by count and 25 as sum value. And same will be case for Sara... you will have same results. Is this correct?

If this is the right then it works fine on my QV file, when I select a name...

Cheers - DV

[#6]

Now I'm really confused...

When I recreate the example I have it works great when using =SUM ({$<Name, Group={$(=Only(Group))}>} Sales) but when I do the exact same thin with my actual data it doesn't work.

Unfortunately I can't share the real qvw-file but I have attached the example file.

Does anyone have any clue at all why I'm getting different results?

I get a - as soon as I choose a name but if i don't choose anything at all I get a 0.

Is it possible to create a variable instead that holds the groupname of the selected agent so that it looks something like this:

=SUM ({$<Name, Group={VARIABLENAME}>} Sales)

The reason that I don't want to choose the Group manually is because the chart is used in a report that is using banding on the Name-field.

[IAMDV]

Daniel - No problem at all. I totally understand that you can't share the actual data. If you are getting a - when you select a name and getting 0 without any selection means your expression is correct. However if the Name in the orginal data is a string with 2 words (First & Last Name) right? If there is a space between the words then use the single quotes.

If this does not work please assign a variable in the Variable Overiew window and then call the variable in the expression like this $(vMyVariable).

I hope this helps.

Cheers - DV

[#8]

Eureka!! 🙂

The problem is probably similar to the one you explained.

I have - (dash) in the group field values.

This is probably a weird question but how do I add in ' between the value since ' is a character that QV uses to identify text?

''' doesn't work 🙂

=SUM ({$<Name, Group={$WANT TO ADD ' HERE(=Only(Group))WANT TO ADD ' HERE}>} Sales)

Thank you so much for your help, your helping me keep my boss happy!

[IAMDV]

Daniel - That's great! I am happy for you 🙂

If you are referring to a string with blank spaces or special characters within a field then use something like this..

=SUM ({$<Name, Group={$(=Only("Group"))}>} Sales)

However if you are using a variable instead of a string then please use the below...

=SUM ({$<Name, Group={$(={'$(=(vGroup))'}>} Sales)

I hope this helps...

Cheers - DV

[IAMDV]

Daniel - Very quickly, please can you mark the post as answered if it was helpful! Something to cheer-up!

Cheers - DV