topic Re: results for each quarter in QlikView

results for each quarter

Wed, 29 Oct 2014 08:05:41 GMT

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

Re: results for each quarter

morganaaron — Wed, 29 Oct 2014 08:33:05 GMT

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.

Re: results for each quarter

richard_chilvers — Wed, 29 Oct 2014 08:55:36 GMT

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

Re: results for each quarter

Wed, 06 Jul 2016 06:23:17 GMT

Have a look at this:

QuartersMap:
MAPPING LOAD
rowno() as Month,
'Q' & Ceil (rowno()/3) as Quarter
AUTOGENERATE (12);
Temp:
Load
min(OrderDate) as minDate,
max(OrderDate) as maxDate
Resident Orders;
Let varMinDate = Num(Peek('minDate', 0, 'Temp'));
Let varMaxDate = Num(Peek('maxDate', 0, 'Temp'));
DROP Table Temp;
TempCalendar:
LOAD
$(varMinDate) + Iterno()-1 As Num,
Date($(varMinDate) + IterNo() - 1) as TempDate
AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate);
MasterCalendar:
Load
TempDate AS OrderDate,
week(TempDate) As Week,
Year(TempDate) As Year,
Month(TempDate) As Month,
Day(TempDate) As Day,
YeartoDate(TempDate)*-1 as CurYTDFlag,
YeartoDate(TempDate,-1)*-1 as LastYTDFlag,
inyear(TempDate, Monthstart($(varMaxDate)),-1) as RC12,
date(monthstart(TempDate), 'MMM-YYYY') as MonthYear,
ApplyMap('QuartersMap', month(TempDate), Null()) as Quarter,
Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as WeekYear,
WeekDay(TempDate) as WeekDay
Resident TempCalendar
Order By TempDate ASC;
Drop Table TempCalendar;