12 Replies Latest reply on May 23, 2017 5:26 AM by Sonja Mynttinen

# Fixed function

Hi! How can get a fixed subset of the whole data? I've tried the next:

{ FIXED [Selected backgroundvariable], [Variable1] : COUNTD([ID])}.

These both variables are from the same table and first one is a calculated field from a parameter. Help appreciated!!

• ###### 1. Re: Fixed function

Hi Sonja,

I can help you but I need to understand your question. Can you explain a little bit more about what you want to achieve?

What is the whole data and what is the subset? You can add a screenshot of your data structure or a dummy sample data or what your expected result should be.

Ossai

• ###### 2. Re: Fixed function

Hi, the subset would be a set of the observations that satisfy the both conditions; they belong to the group of Variable1 and to the group of the selected background variable. So I want to take to intersection of these two sets mathematically speaking.. where the ID is the unique key...!

• ###### 3. Re: Fixed function

Can you post a sample data showing what you want to do? It doesn't need to be real data.

A screenshot of your dimensions and what you want to count will be okay also.

• ###### 5. Re: Fixed function

I would like to get the percentage of office supplies over all categories in a certain year and country, lets say 2011 in Ireland... I hope I made my self more clear !

• ###### 6. Re: Fixed function

This will do

{Fixed [category]:SUM(IIF([category] ='office supplies' AND Year([Order Date])=2011 AND [country] ='Ireland',[sales],NULL))}

/

{Fixed [category]:SUM(IIF([country]='Ireland' AND Year([order date])=2011,[sales],NULL))} // of all countries. If you want Ireland then substitute SUM([sales])

• ###### 7. Re: Fixed function

Sonja,

Here's my approach. It doesn't require explicitly defining any category, year or country in the formula. It will also work when you filter by any dimension.

1. Create calculated field [Year]

YEAR([Order Date])

2. Create calculated field [% Total Sales]

{FIXED [Year], [Category], [Country]: SUM([Sales])} / {FIXED [Year], [Country]: SUM([Sales])}

See attached workbook. Hope this helps.

Ossai

• ###### 8. Re: Fixed function

Thanks a lot for the answers, but I guess I have to rephrase my question. I have a question(variable) that you can answer with choosing multiple options. How can I get percentage "how many picked 'a' " / "how many answered to that question overall".

• ###### 9. Re: Fixed function

{ FIxed question(variable): COUNT(IIF(question(variable)='a', question(variable),NULL))}

/

{ FIxed question(variable): COUNT(question(variable))}

• ###### 10. Re: Fixed function

And one more thing; I want the end user to be able to select his option through a filter, I guess the previous works only for a constant a. The problem is when calculating the percent, I want to filter the numerator and denominator through certain background variables. I want to know for example how many 20-30 year old men who have answered the question overall have selected "what ever the end user decides to select (even several picks)".

• ###### 11. Re: Fixed function

Hi Sonja,

How does this new question relate to the previous one? Do you have a response variable (a, b, c, etc) for different countries and years? I'd like to know how many dimensions you have and how your data is structured. It will be difficult to give you a solution if you don't provide me with full information. Better still, you can mock up a dummy data set and attach it here.

Ossai

• ###### 12. Re: Fixed function

My problem was quite complicated to explain here without the original data, but I managed to find the solution, thanks for help!