-
1. Re: Fixed function
Okechukwu OssaiMay 19, 2017 5:06 AM (in response to Sonja Mynttinen)
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
Sonja Mynttinen May 19, 2017 6:15 AM (in response to Okechukwu Ossai)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
Okechukwu OssaiMay 19, 2017 6:30 AM (in response to Sonja Mynttinen)
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
Sonja Mynttinen May 21, 2017 5:25 AM (in response to Sonja Mynttinen)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
Prayson Wilfred Daniel May 21, 2017 6:11 AM (in response to Sonja Mynttinen)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
Okechukwu OssaiMay 21, 2017 4:27 PM (in response to Sonja Mynttinen)
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
-
Fixed Function_OO.twbx 1.2 MB
-
-
8. Re: Fixed function
Sonja Mynttinen May 22, 2017 2:52 AM (in response to Sonja Mynttinen)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
Prayson Wilfred Daniel May 22, 2017 3:11 AM (in response to Sonja Mynttinen){ FIxed question(variable): COUNT(IIF(question(variable)='a', question(variable),NULL))}
/
{ FIxed question(variable): COUNT(question(variable))}
-
10. Re: Fixed function
Sonja Mynttinen May 22, 2017 4:17 AM (in response to Sonja Mynttinen)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
Okechukwu OssaiMay 22, 2017 4:16 AM (in response to Sonja Mynttinen)
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
Sonja Mynttinen May 23, 2017 5:26 AM (in response to Sonja Mynttinen)My problem was quite complicated to explain here without the original data, but I managed to find the solution, thanks for help!