Need help on chart table expression

[#1]

Hi all,

I am trying to convert label array into new table. I've got all values in each label and they OK, but when I am trying to put same label expression into table QlikView executes only first value and other ones ignored. How can I force executing all conditions in expression?

My Dimension expression:

=ValueList( Dual('Previous',1),  Dual('Current',2),  Dual('Change',3))

Expression value:

=pick(match(
  ValueList( Dual('Previous',1),  Dual('Current',2),  Dual('Change',3))
  , Dual('Previous',1)
  , Dual('Current',2)
  , Dual('Change',3)
  )
, // PREVIOUS
Sum({1}IF(Aggr(Count(
  {1<[Member Purchasing Group]={"$(=GetFieldSelections([Purchasing Group]))"}
  , [Posting Date] = {">=$(=$(vSelPreviousStartDate))<$(=$(vSelPreviousEndDate))"}
  , FrameTransCount = {1}>}
  DISTINCT [Framework Description]),[Member Code])=1, 1))
, // CURRENT
Sum({1}IF(Aggr(Count(
  {1<[Member Purchasing Group]={"$(=GetFieldSelections([Purchasing Group]))"}
  , [Posting Date] = {">=$(=$(vSelCurrentStartDate))<$(=$(vSelCurrentEndDate))"}
  , FrameTransCount = {1}>}
  DISTINCT [Framework Description]),[Member Code])=1, 1))
, // CHANGE / DIFFERENCE


//CURRENT
Sum({1}IF(Aggr(Count(
  {1<[Member Purchasing Group]={"$(=GetFieldSelections([Purchasing Group]))"}
  , [Posting Date] = {">=$(=$(vSelCurrentStartDate))<$(=$(vSelCurrentEndDate))"}
  , FrameTransCount = {1}>}
  DISTINCT [Framework Description]),[Member Code])=1, 1))
- // PREVIOUS
Sum({1}IF(Aggr(Count(
  {1<[Member Purchasing Group]={"$(=GetFieldSelections([Purchasing Group]))"}
  , [Posting Date] = {">=$(=$(vSelPreviousStartDate))<$(=$(vSelPreviousEndDate))"}
  , FrameTransCount = {1}>}
  DISTINCT [Framework Description]),[Member Code])=1, 1))
)

This should return data into 3 different columns: Previous, Current and Change values. Currently it returns only first Previous column. Moving part of expression into label value is displayed correctly. Also I will have to duplicate this formula 10 times as this returns only for framework count 1, then I will need to have 2 and etc up to 10 by changing single numeric value from

DISTINCT [Framework Description]),[Member Code])=1, 1))

to

DISTINCT [Framework Description]),[Member Code])=2, 1))

Also tried this using IF statement and did not worked too.

Is it possible to combine all this into single expression maybe?

My current results looks like this and contains only Previous values:

	Previous	Current	Change
Periods	Apr 2013  to Nov 2013	Apr 2014  to Nov 2014
Trading on 1 framework	248	0	0
Trading on 2 frameworks	123	0	0
Trading on 3 frameworks	93	0	0
Trading on 4 frameworks	77	0	0
Trading on 5 frameworks	35	0	0
Trading on 6 frameworks	36	0	0
Trading on 7 frameworks	9	0	0
Trading on 8 frameworks	7	0	0
Trading on 9 frameworks	2	0	0
Trading on 10 or more frameworks	0	0	0

Thanks in advance.

[Gysbert_Wassenaar]

Can you post a .qvw document that demonstrates the problem?

[#3]

I can't put original file as too big. I'll maybe try to create a new one and then post it.

[#4]

Hi, I've create a simple example,

Added approx. data and created a red table. Trading members on 1 framework previous has 11 members, but current has 0 as well as changed.

I've moved same logic into labels below table for same row and they are displaying correctly.

Also I've tried to use different approach with blue table but I get wrong results. Did not checked on sample but I am sure the label values are correct as I get same in my live version.

[Gysbert_Wassenaar · Accepted solution]

See attached qvw.

[#6]

Hi Gysbert,

I found a small issue with single query 10 plus. It does not return correct values. I've added new parameter to pass number for testing on any numbers. As test used trading on 4 and more frameworks and returns 0 values but should return 1 as there is single member currently trading in on 5 frameworks.

Same happens with 10 plus. See attachment with red line.

[Gysbert_Wassenaar]

I found a small issue with single query 10 plus. It does not return correct values.

You changed the expression in the variable to accept a third parameter. The way you did that is almost, but not quite correct. Try this expression instead:

count({1< [Member Code]={'=Count(

  {1<[Member Purchasing Group]={"$(=only([Purchasing Group]))"}

  , [Posting Date] = {">=$(=$($1))<$(=$($2))"}

  , FrameTransCount = {1}>}

  DISTINCT [Framework Description])>=$3'}>} distinct [Member Code] )