How to get the Quarterly data calculation in set expression

Karim_Khan — Tue, 04 Aug 2015 15:26:09 GMT

I am applying the below code

=sum({<FiscalYear={'$(vPriorYear)'},FiscalQuarter={'$(vQuarter)'}>}[Net Bookings])

this is working fine for first month calculation of particular quarter.But while selecting the last or middle month of any quarter then calculation is showing wrong value

Regards,

K K

Re: How to get the Quarterly data calculation in set expression

ramoncova06 — Tue, 04 Aug 2015 15:55:39 GMT

can you upload some sample data, since you are using variables we cannot tell what values are they bringing

Re: How to get the Quarterly data calculation in set expression

Karim_Khan — Tue, 04 Aug 2015 16:06:14 GMT

SET ThousandSep=',';

SET DecimalSep='.';

SET MoneyThousandSep=',';

SET MoneyDecimalSep='.';

SET MoneyFormat='Rs. #,##0.00;Rs. -#,##0.00';

SET TimeFormat='hh:mm:ss';

SET DateFormat='DD-MM-YYYY';

SET TimestampFormat='DD-MM-YYYY hh:mm:ss[.fff]';

SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';

SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';

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,

Day(date#([Fiscal Period ID],'YYYYMM')) as FiscalDay,

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 (Cisco)

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

//LOAD

//FiscalYear,

//FiscalMonthNum,

//CalenderMonth,

//[Fiscal Period ID],

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

//Resident Cisco;

//DROP Table Cisco;

Re: How to get the Quarterly data calculation in set expression

ramoncova06 — Tue, 04 Aug 2015 16:21:08 GMT

your script looks ok, though I would suggest creating a master calendar

The Master Calendar

what is inside of your variables ?

topic Re: How to get the Quarterly data calculation in set expression in QlikView

How to get the Quarterly data calculation in set expression

Re: How to get the Quarterly data calculation in set expression

Re: How to get the Quarterly data calculation in set expression

Re: How to get the Quarterly data calculation in set expression