QTD calculation issue

Karim_Khan — Wed, 05 Aug 2015 14:20:00 GMT

Pls help

while am applying the thing on set expression for QTD calculation its not working properly.

Sum({<Quarter=, Month=, Quarter=, Week=, Date=,DateNum={'>=$(=Num(QuarterStart(vMaxDate), 0, 8))<=$(=vMaxDate)'}>} [Net Bookings])

Pls check my below script

Cisco:

LOAD [End Customer Global Ultimate Name],

Country,

[End Customer HQ Top],

[Sales Level 1],

[Sales Level 2],

[Sales Level 3],

[Sales Level 4],

[Sales Level 5],

[Partner Name],

[Fiscal Period ID],

Year(date#([Fiscal Period ID],'YYYYMM')) as FiscalYear,

num(month(date#([Fiscal Period ID],'YYYYMM'))) as FiscalMonthNum,

month(date#([Fiscal Period ID],'YYYYMM')) as FiscalMonth,

Date(date#([Fiscal Period ID],'YYYYMM'),'DD-MM-YYYY') as Date,

FMV,

[Net Bookings],

Group

FROM

[\\arlmssan02\cm$\tp_managers\DBMIGrp\9. Team Members Folders\Karim\Assign_Project\Nikhil.G\CISCO\Copy of June raw.xlsx]

(ooxml, embedded labels, table is Sheet1);

Left Join

LOAD * Inline [

FiscalMonthNum,CalenderMonth, CaldenderMonthNum,FiscalQuarter

1,Aug,8,Q1

2,Sep,9,Q1

3,Oct,10,Q1

4,Nov,11,Q2

5,Dec,12,Q2

6,Jan,1,Q2

7,Feb,2,Q3

8,Mar,3,Q3

9,Apr,4,Q3

10,May,5,Q4

11,Jun,6,Q4

12,Jul,7,Q4 ];

//Final:

//LOAD *,

//monthname(Date#(CalenderYear&CalenderMonth,'YYYYMMM')) as CalenderMonthYear,

//date(Date#(CalenderYear&CalenderMonth,'YYYYMMM'),'DD-MM-YYYY') as CalenderDate;

//LOAD

//FiscalYear,

//FiscalMonthNum,

//CalenderMonth,

//[Fiscal Period ID],

//if(CaldenderMonthNum>=8,FiscalYear-1,FiscalYear) as CalenderYear

//Resident Cisco;

//////

//DROP Table Final;

QuartersMap:

MAPPING LOAD

rowno() as Month,

'Q' & Ceil (rowno()/3) as Quarter

AUTOGENERATE (12);

Temp:

Load

min(Date) as minDate,

max(Date) as maxDate

Resident Cisco;

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 Date,

week(TempDate) As Week,

Year(TempDate) As Year,

Month(TempDate) As Month,

Day(TempDate) As Day,

YeartoDate(TempDate)*-1 as CYTD,

YeartoDate(TempDate,-1)*-1 as LYTD,

inyear(TempDate, Monthstart($(varMaxDate)),-1) as Rolling12,

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;

Regards,

Re: QTD calculation issue

jonathandienst — Wed, 05 Aug 2015 14:30:02 GMT

'not working properly' is rather vague. What exactly is going wrong and what did you expect and what have you tried to resolve it?

How have you defined the variable vMaxDate?

Did you try with a literal date?

You probably don't want that Num() in the expression.

Re: QTD calculation issue

Karim_Khan — Wed, 05 Aug 2015 14:38:47 GMT

Hi,

I have tried to make the changes through

Sum({<Quarter=, Month=, Quarter=, Week=, Date=,DateNum={'>=$(=Num(QuarterStart(vMaxDate), 0, 8))<=$(=vMaxDate)'}>} [Net Bookings])

but calculatuion is showing worng. and If try with

=SUM({<Year={'$(VCurrentYear)'},Quarter={'$(vQuarter)'},Day={'<=$(vMaxDay)'},Date=,MonthYear=>}[Net Bookings])

then it gives me Same calculation as of Monthly calculation.

Pls suggest

Re: QTD calculation issue

jonathandienst — Wed, 05 Aug 2015 14:44:17 GMT

How have you defined the variable vMaxDate?

Did you try with a literal date?

You probably don't want that Num() in the expression.

Re: QTD calculation issue

Karim_Khan — Wed, 05 Aug 2015 14:46:50 GMT

I have used the vMaxDate in Expression variable to get the Maxdate

topic Re: QTD calculation issue in QlikView

QTD calculation issue

Re: QTD calculation issue

Re: QTD calculation issue

Re: QTD calculation issue

Re: QTD calculation issue