Quick set analysis question

[#1]

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

[Michiel_QV_Fan]

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.....)

[#3]

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

[Michiel_QV_Fan]

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

[#5]

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

[#6]

*as text

[Michiel_QV_Fan]

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;