topic Re: Use alternate state one per dimension value in QlikView

Use alternate state one per dimension value

Fri, 27 Nov 2015 09:45:54 GMT

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?

Re: Use alternate state one per dimension value

sunny_talwar — Fri, 27 Nov 2015 13:06:24 GMT

Would you be able to share a sample?

Re: Use alternate state one per dimension value

swuehl — Fri, 27 Nov 2015 13:07:00 GMT

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)?

Re: Use alternate state one per dimension value

Fri, 27 Nov 2015 13:29:45 GMT

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.

Re: Use alternate state one per dimension value

sunny_talwar — Fri, 27 Nov 2015 14:12:21 GMT

See if this expression helps:

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

Re: Use alternate state one per dimension value

Fri, 27 Nov 2015 14:21:26 GMT

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...

Re: Use alternate state one per dimension value

sunny_talwar — Fri, 27 Nov 2015 14:57:28 GMT

May be this:

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

Re: Use alternate state one per dimension value

Tue, 01 Dec 2015 11:04:06 GMT

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())

Re: Use alternate state one per dimension value

sunny_talwar — Tue, 01 Dec 2015 12:34:57 GMT

Seems to be working for me. Check it out:

Re: Use alternate state one per dimension value

Tue, 01 Dec 2015 15:35:47 GMT

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.

Re: Use alternate state one per dimension value

sunny_talwar — Tue, 01 Dec 2015 17:49:35 GMT

Hopefully this:

Expression ->

If(Count(TOTAL <Employee>{[Alt1]<FileName={'D17.11.2015'}>} [Order Date]) = Count(TOTAL <Employee>{[Alt2]<FileName = {'D24.11.2015'}>} [Order Date]), Black(),

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

Re: Use alternate state one per dimension value

Wed, 02 Dec 2015 08:28:49 GMT

I am afraid but it does not work as desired.

That is what i get, when I do not select anything. For # Deliveries it seems to work correctly but # Orders (Alice) the 3 should be green whereas the 1 should be red. And # Orders (Bob) the 1 should be red and the 2 green.

I also do not understand what "<Employee>" in front of set analysis does, could you explain that?

Re: Use alternate state one per dimension value

Wed, 02 Dec 2015 10:03:48 GMT

I think I found the solution. I had to change the expression for eMaxOrder to

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

Re: Use alternate state one per dimension value

sunny_talwar — Wed, 02 Dec 2015 17:39:48 GMT

That is awesome . Let us know if you still have any problem

Re: Use alternate state one per dimension value

Thu, 31 Mar 2016 03:50:24 GMT

Hi Sunny, I am wondering if FileName field contains many values, how do I deal with it.

Re: Use alternate state one per dimension value

sunny_talwar — Thu, 31 Mar 2016 09:06:03 GMT

What do you mean when you say multiple values? Do you have an example?