5 Replies Latest reply: Jun 13, 2017 4:37 AM by Глеб Аитов

# Transform selections in the field by a common rule

Hi,

I have the following data:

Product

Alpha 100

Beta 200
Theta 300
Gamma 400

I have set a new \$(vPY) variable, which takes the selected field, subtracts certain number (say, 15) from the numeric part and returns the value. In set analysis I then use this variable as in the following example: SUM ( {< Product={ "\$(=\$(vPY) )" } >} Sales)

Products selected\$(vPY)

Alpha 100

Alpha 85
Beta 200Beta 185
Theta 300Theta 285
Gamma 400Gamma 385

Question 1: when multiple fields are selected, it returns nothing. What i need it to return is as follows, so i could continue to use it in set analysis:

Products selected\$(vPY)
Alpha 100, Beta 200Alpha 85, Beta 185

Question 2: when nothing is selected in the FIELD, the variable returns nothing. Effectively the formula gives the following result SUM ( {< Product={ } >} Sales), and thus returns zero sales. I need to tell Qlik to disregard the Product dimension, when nothing is selected in the Product field.

So far I managed to do it with the IF statement, but this is clumsy (if(GetSelectedCount(Product)=0, SUM (Sales), SUM ( {< Product={ "\$(=\$(vPY) )" } >} Sales)

Thank you!

• ###### Re: Transform selections in the field by a common rule

How is vPY defined?

• ###### Re: Transform selections in the field by a common rule

if (Upper("Season")=Upper('allseason'),'allseason',

if (SubstringCount(Upper("Season"), 'SS')>0, '[Season]={'&chr(39)&(Left("Season",2)-1)&' ss'&chr(39)&'}',

if (SubstringCount(Upper("Season"), 'FW')>0, '[Season]={'&chr(39)&(Left("Season",2)-1)&'-'&(Mid("Season",4,2)-1)&' fw'&chr(39)&'}',

'[Season]='

)))

• ###### Re: Transform selections in the field by a common rule

What is seasons here? Would you be able to share a sample to look at?

• ###### Re: Transform selections in the field by a common rule

season is the collection of apparel sold in particular year.

The values are as follows:

ss 15, ss 16, ss 17 and etc.

fw 15-16, fw 16-17, fw 17-18 and etc.

The formula works as follows:

when i choose ss 16, the variable returns Season={'ss 15'}, when i choose fw 16-17, the variable returns Season={'fw 16-17'}

When more than one season values are selected ss 16 and fw 16-17, the variable returns nothing.

I want it to return Season={'ss 15, fw 15-16'}

It might be a solution which just came to my mind:

1. what if i create a second field name SeasonCompare

2. In the script I predefine a "pair" for each season, by putting a formula from vPY, it will create the following table:

SeasonSeasonComp
ss 16ss 15
fw 16-17fw 15-16

3. Than in the set analysis: SUM ( {< Season=SeasonComp>} Sales ).  So when I choose multiple values in the field Season, like ss 16 AND fw 16-17, it will show the sales for values from the SeasonCompare field. Does that make sense?

it's quite hard to share the sample, sorry. It is a part of a large data set.