Transform selections in the field by a common rule

[ziabobaz]

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 200	Beta 185
Theta 300	Theta 285
Gamma 400	Gamma 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 200	Alpha 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!

[ziabobaz · Accepted solution]

the answer to Question 2 is below

Set Analysis - Ignore all selections except certain field

[sunny_talwar]

How is vPY defined?

[ziabobaz]

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]='

    )))

[sunny_talwar]

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

[ziabobaz]

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:

Season	SeasonComp
ss 16	ss 15
fw 16-17	fw 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.