Problem with set analysis ?

Anonymous — Sat, 05 Nov 2011 16:10:51 GMT

Hi Guys,

Question is about set analysis and pivot tables - this time i want to show the sum of all values in a column.

This is my formula:

=If(GLLedger = 'N',Sum({$<Month =>} If(InYearToDate(GLTranDate, vMaxDate, 0, 7), GLTranAmount)))

This sums at partial sum levels but not as a grand total. I've tried to re-arrange the formula to nest the IF statement inside the Sum statement, but this does not work.

Thx

//***Initialising variables for deriving fiscal years

Set vFiscalMonth = 7 ; // 1st month of FiscalYr is July

Set vFiscalDay = 6; // 1st Day of the week (0=Mon, 1=Tue, ... , 6=Sun)

MasterCalendar:

Load distinct

FYear&chr(32)&FiscalMonth as FiscalYrMonth,

Load distinct

FYear&chr(32)&Dual('Q' & Ceil(FMonth/3), Ceil(FMonth/3)) as FiscalYrQtr,

Dual(FYear-1 &'/'& FYear, FYear) as FiscalYear, // Dual fiscal year

Dual(Month, FMonth) as FiscalMonth, // Dual fiscal month

Dual('Q' & Ceil(FMonth/3), Ceil(FMonth/3)) as FiscalQuarter,

Ceil((OrderDate-StartOfFWeekOne+1)/7) as FiscalWeekNo,

Load *,

Year&'-'&Quarter as YrQtr,

Year + If(Month>=$(vFiscalMonth), 1, 0) as FYear, // Numeric fiscal year

Mod(Month-$(vFiscalMonth), 12)+1 as FMonth, // Numeric fiscal month

WeekStart(FiscalYearStart,0,$(vFiscalDay)) as StartOfFWeekOne

;

Load

TempDate AS OrderDate,

TempDate AS Date,

week(TempDate) As Week,

Date(Monthstart(TempDate), 'MM/DD/YYYY') as MonthStartYear,

Date(Monthstart(TempDate), 'MMM-YYYY') as MonthYear,

YearStart(TempDate,0,$(vFiscalMonth)) as FiscalYearStart,

Year(TempDate) as Year,

Month(TempDate) as Month,

Day(TempDate) as Day,

'Q' & ceil(month(TempDate) / 3) as Quarter,

Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as WeekYear,

WeekDay(TempDate) as WeekDay

;

//=== Generate a temp table of dates ===

LOAD

date(mindate + IterNo()) AS TempDate

,maxdate // Used in InYearToDate() above, but not kept

WHILE mindate + IterNo() <= maxdate;

//=== Get min/max dates from Field ===/

LOAD

min(FieldValue('OrderDate', recno()))-1 as mindate,

max(FieldValue('OrderDate', recno())) as maxdate

AUTOGENERATE FieldValueCount('OrderDate');

left join (MasterCalendar)//Integrating Fiscal Period

LOAD * INLINE [

MonthStartYear, FYear, FiscalPeriod

07/01/2006, 2007, 1

08/01/2006, 2007, 2

09/01/2006, 2007, 3

10/01/2006, 2007, 4

11/01/2006, 2007, 5

12/01/2006, 2007, 6

01/01/2007, 2007, 7

02/01/2007, 2007, 8

03/01/2007, 2007, 9

04/01/2007, 2007, 10

05/01/2007, 2007, 11

06/01/2007, 2007, 12

07/01/2007, 2008, 1

08/01/2007, 2008, 2

09/01/2007, 2008, 3

10/01/2007, 2008, 4

11/01/2007, 2008, 5

12/01/2007, 2008, 6

01/01/2008, 2008, 7

02/01/2008, 2008, 8

03/01/2008, 2008, 9

04/01/2008, 2008, 10

05/01/2008, 2008, 11

06/01/2008, 2008, 12

07/01/2008, 2009, 1

08/01/2008, 2009, 2

09/01/2008, 2009, 3

10/01/2008, 2009, 4

11/01/2008, 2009, 5

12/01/2008, 2009, 6

01/01/2009, 2009, 7

02/01/2009, 2009, 8

03/01/2009, 2009, 9

04/01/2009, 2009, 10

05/01/2009, 2009, 11

06/01/2009, 2009, 12

];

AsOf:

LOAD DISTINCT Date as AsOfDate

RESIDENT MasterCalendar

;

LEFT JOIN (AsOf)

LOAD AsOfDate as Date

RESIDENT AsOf

;

INNER JOIN (AsOf)

LOAD *,1 as YTD

RESIDENT AsOf

WHERE year(AsOfDate) = year( Date)

AND AsOfDate >= Date

;

LEFT JOIN (AsOf)

LOAD *

,if(quarterstart(AsOfDate)=quarterstart( Date),1) as QTD

,if(monthstart(AsOfDate)=monthstart( Date),1) as MTD

RESIDENT AsOf;

Problem with set analysis ?

swarup_malli — Sat, 05 Nov 2011 16:16:25 GMT

Balraj,

Try this formula.

Below is the solution:

=Sum({$<Month =>} If(InYearToDate(GLTranDate, vMaxDate, 0, 7), If(GLLedger = 'Y',GLTranAmount)))

Regards

Swarup

Problem with set analysis ?

Anonymous — Sat, 05 Nov 2011 16:17:36 GMT

Thnx for the Quick responce....let me check it ...

Problem with set analysis ?

swarup_malli — Sat, 05 Nov 2011 16:22:45 GMT

No problem

topic Problem with set analysis ? in QlikView

Problem with set analysis ?

Problem with set analysis ?

Problem with set analysis ?

Problem with set analysis ?