Use alternate state one per dimension value

[#1]

Hello!

I am trying to use alternative states to solve my problem. I have two datasets with the same data but they are different snapshots. Except from one sheet all diagrams only take one dataset (specified by flag 'fileName') into concern. On one sheet my goal is to compare those two sets using 8 different expressions in a pivot table. Right now I use 'FileName' as dimension and something like 'Sum({$<FileName = >} Distance)' as expression. That works fine but now there should be two list boxes (for Group1 and Group2) which filter only one dataset (selections made in Group1 should only influence dataset1, selections in Group2 sould only affect dataset2).

As far as I understand alternative states they work that way that two expressions (for the different group) are calculated for each dimension value, but I want to use one state for echt dimension value.

I attached a file of my actual pivot table, but right now, it is not possible to do alternative selections for regions of the two different files.

Does anyone know if this is possible and if yes how?

[sunny_talwar]

Would you be able to share a sample?

[swuehl]

It's a bit unclear how your setting looks like and what you are trying to achieve, and the screenshot is not helping much in here. Could you upload a small QVW that represents your setting (could contain mock up data if needed)?

[#4]

I added a QVW. Let me explain what i intend to get:

I want to select "Alice" for File "D17.11.2015" and and select "Bob" for File "D24.11.2015" to compare the # of Orders and Deliveries Alice placed last week and Bob placed this week. Note that in my production system I have more sheets with diagrams and for that reason there exists a listbox "FileName" where always one value has to be selected.

[sunny_talwar]

See if this expression helps:

Count({[Alt1]<FileName = {'D17.11.2015'}> + [Alt2]<FileName = {'D24.11.2015'}>} [Order Date])

[#6]

Thanks, this approach looks good. What possibility do I have if I do not want to hard code the two filenames? There are always only 2 possibilities, but if i get newer data i do not want to always manualle edit the set expressions...

[sunny_talwar · Accepted solution]

May be this:

Count({[Alt1]<FileName = {"$(=SubField(Concat({1}DISTINCT FileName, '|'), '|', 1))"}> + [Alt2]<FileName = {"$(=SubField(Concat({1}DISTINCT FileName, '|'), '|', 2))"}>} [Order Date])

[#8]

Thank you for the great solution.

Is there any possibility to set color (green) for the higher value and another color (red) for the lower value (as i always have 2 values per expression)?

I get the maximum of the two values using

Max({$<FileName =>}Aggr(Count({[Alt1]<FileName={'D17.11.2015'}> + [Alt2]<FileName = {'D24.11.2015'}>} [Order Date]), FileName)))

and store it in the variable eMaxOrder

But when I use this variable to define the text color it does not work correctly. I try to compare the actual value of [# Orders] with the variable to set the color, but using the following expression both values for [# Orders] are colored green

If([# Orders] = $(eMaxOrder), Green(), LightRed())

[sunny_talwar]

Seems to be working for me. Check it out:

[#10]

I forgot to add the most importent thing (sorry!), i added "Employee" as dimension and it is on top (before "FileName").

I attatched the actual file. Please notice that my If-Clause increased because I do not want the order number colored if they both have the same value; in that case I want them to stay black.