Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
the answer to Question 2 is below
the answer to Question 2 is below
How is vPY defined?
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]='
)))
What is seasons here? Would you be able to share a sample to look at?
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.