results for each quarter

[#1]

Hi,

Hoping people can help with this problem that's comes up quite regular.

On the attached we want to produce straight tables that will show results on a question each quarter. Can only ever seem to show the same quarter.

On the atatched we have data for Q1 and Q2 so want to show the breakdown.

Any help would be greatly appreciated.

Chris

[morganaaron]

Not quite sure I fully understand what you're asking for, but if you want to breakdown the data by quarter you can try two things:

1) Put Quarter in as a dimension into your table (as a secondary dimension) and then Sort by your Text field so the questions remain in 1 to 4 order.

2) Create two straight tables and use set analysis like: Count({<Quarter={'Q1'}>}Quarter) to limit it just to a particular quarter.

I think your main problem might be your data however - it might be simpler to load a table with quarter, question and then number of responses rather than repeating the quarter over and over for each response. Either that or create a responses table if you want to drill down into individual detail for each person responding.

Hope that helps.

[richard_chilvers]

As Aaron says, it would be useful to have more clarity on what your complete data will be. In your example, you have just 2 quarters, but I'm guessing you (will have) alot more. This will impact the best solution, but it looks like it will be straight forward to resolve.

Regards

[#4]

Have a look at this:

1. QuartersMap: 
2. MAPPING LOAD  
3. rowno() as Month, 
4. 'Q' & Ceil (rowno()/3) as Quarter 
5. AUTOGENERATE (12); 
6.  
7. Temp: 
8. Load 
9.                min(OrderDate) as minDate, 
10.                max(OrderDate) as maxDate 
11. Resident Orders; 
12.  
13. Let varMinDate = Num(Peek('minDate', 0, 'Temp')); 
14. Let varMaxDate = Num(Peek('maxDate', 0, 'Temp')); 
15. DROP Table Temp; 
16.  
17. TempCalendar: 
18. LOAD 
19.                $(varMinDate) + Iterno()-1 As Num, 
20.                Date($(varMinDate) + IterNo() - 1) as TempDate 
21.                AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate); 
22.  
23. MasterCalendar: 
24. Load 
25.                TempDate AS OrderDate, 
26.                week(TempDate) As Week, 
27.                Year(TempDate) As Year, 
28.                Month(TempDate) As Month, 
29.                Day(TempDate) As Day, 
30.                YeartoDate(TempDate)*-1 as CurYTDFlag, 
31.                YeartoDate(TempDate,-1)*-1 as LastYTDFlag, 
32.                inyear(TempDate, Monthstart($(varMaxDate)),-1) as RC12, 
33.                date(monthstart(TempDate), 'MMM-YYYY') as MonthYear, 
34.                ApplyMap('QuartersMap', month(TempDate), Null()) as Quarter, 
35.                Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as WeekYear, 
36.                WeekDay(TempDate) as WeekDay 
37. Resident TempCalendar 
38. Order By TempDate ASC; 
39. Drop Table TempCalendar;