Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
ziabobaz
Creator III
Creator III

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!

1 Solution

Accepted Solutions
ziabobaz
Creator III
Creator III
Author

5 Replies
ziabobaz
Creator III
Creator III
Author

sunny_talwar

How is vPY defined?

ziabobaz
Creator III
Creator III
Author

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
Creator III
Creator III
Author

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.