topic Re: Quick set analysis question in QlikView

Quick set analysis question

Fri, 10 Jan 2014 11:38:33 GMT

HI,

i'm new to qlikview and sql and so can't quite work out how to just return data for the current quarter

SUM({$<CalendarQuarterAndYear={$(=CalendarQuarterAndYear(TODAY()))}>} Activities.number_of_individual_activities__c)

and also the previous quarter

SUM({$<CalendarQuarterAndYear={$(=CalendarQuarterAndYear(TODAY())-1)}>} Activities.number_of_individual_activities__c)

The above is my best shot, but it doesn't seem to work

thanks very much

Alex

Re: Quick set analysis question

Michiel_QV_Fan — Fri, 10 Jan 2014 12:33:21 GMT

Barry Harmsen has a great solution for this in his book.

Add a period ID for the CalendarQuarterAndYear dimension using Autonumber in your load script. The select in your set analysis the max periodID.

Then it would be soemthing like this:

sum({<PeriodID = {"$(=max(PeriodID)"}>}Activities.number.....)

Re: Quick set analysis question

Fri, 10 Jan 2014 13:22:16 GMT

OK, but what would I need to add to my script? I'm a bit new to this all, sorry!

Could I use variable instead? I have a varCurrentQuarter available as a variable, but am not sure how I would use it im the set analysis

Re: Quick set analysis question

Michiel_QV_Fan — Fri, 10 Jan 2014 13:38:02 GMT

What data is shown in this variable? Just a quater number or combined with a year?

Re: Quick set analysis question

Fri, 10 Jan 2014 14:05:26 GMT

It's a number, but only because I can't work out how to format it as a test

I've tried QQ-YYYY, but this didn't work

Re: Quick set analysis question

Fri, 10 Jan 2014 14:18:58 GMT

*as text

Re: Quick set analysis question

Michiel_QV_Fan — Fri, 10 Jan 2014 14:21:49 GMT

I Think you need a a master calendar, Copy and paste:

Change 2 things:

'set calendar_table' to your table name which contains the date

and

'set Datum' to your date field name

//Set variables for calendar

//Set calender source table

set calendar_table = 'LineasVentas';

//set calender date field

set Datum = 'Date';

//Load unique date values from source table

Kalender_limiter:

Load Distinct $(Datum) as Kalender_limiter

Resident $(calendar_table);

Kalender:

TMP:

NoConcatenate

load

max($(Datum)) as MaxDate,

min($(Datum)) as MinDate

Resident $(calendar_table);

let vDateStart = peek('MinDate', 0);

let vDateEnd = peek('MaxDate', 0);

drop table TMP;

TEMPCALENDAR:

LOAD

'$(vDateStart)' + ITERNO()-1 AS DateNo,

DATE('$(vDateStart)' + ITERNO() -1) AS Date

AUTOGENERATE(1) WHILE

'$(vDateStart)' + ITERNO() -1 <= '$(vDateEnd)';

pre_Kalender:

Load *,

AutoNumber(Jaar, 'Period_jaarID') as Period_jaarID,

AutoNumber(JaarKwartaal, 'Period_kwartaalID') as Period_kwartaalID,

AutoNumber(JaarMaand, 'Period_monthID') as Period_monthID,

AutoNumber(JaarWeek, 'Period_weekID') as Period_weekID,

AutoNumber($(Datum), 'Period_datumID') as Period_datumID

;

LOAD

date(Date) AS $(Datum),

date(Date) AS Grootboekdatum,

WEEK(Date) AS Week,

year(Date) as Jaar,

MONTH(Date) AS Maand,

RIGHT('00' & NUM(MONTH(Date)), 2) AS Maandnr,

'Q' & CEIL(NUM(MONTH(Date))/3) AS Kwartaal,

DAY(Date) AS Dag,

weekYEAR(Date) & '-' & right('0' &WEEK(Date),2) AS JaarWeek,

YEAR(Date) & '-' & right('0' & NUM(MONTH(Date)),2) AS JaarMaand,

YEAR(Date) & '-Q' & CEIL(NUM(MONTH(Date))/3) AS JaarKwartaal,

WeekDay(Date) as Dagnaam,

num(month(today())) as HuidigeMaand

RESIDENT

TEMPCALENDAR

ORDER BY Date;

Kalender:

NoConcatenate

Load * Resident pre_Kalender

where Exists (Kalender_limiter, $(Datum));

drop table TEMPCALENDAR;

drop table pre_Kalender;

drop table Kalender_limiter;