how to get the sum of first month till selected month in qlikview with example

[Karim_Khan]

how to get the sum of first month till selected month in qlikview with example

Regards,

Karim Khan

[Karim_Khan]

My concern is that I want the sum of Data from Aug Month to Till Selected Month

[sunny_talwar]

What is the format of FiscalMonthName field??? Something like Aug-2015?

[Karim_Khan]

No Just Aug.

[sunny_talwar]

I would recommend not using just the Month field as it may get very complicated to make sure you use the correct year of selection. I suggest creating a field like this in the script:

MonthName([Fiscal Date]) as [FiscalMonthYear]

and use that in the set analysis.

Although, I am not sure why you are reluctant on using the date set analysis as it will give you the data from 1/1/2015 to end of the month for your selection.

[Karim_Khan]

Sir I have only Fiscal Period ID.Pls check my Script

Cisco:

LOAD Distinct

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

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

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

     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

//1,Aug,8

//2,Sep,9

//3,OCt,10

//4,Nov,11

//5,Dec,12

//6,Jan,1

//7,Feb,2

//8,Mar,3

//9,Apr,4

//10,May,5

//11,Jun,6

//12,Jul,7 ];

////

//

//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(FisDate) as minDate, 

//               max(FisDate) 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; 

SET vFiscalYearStartMonth = 8;

LET vStartDate = Num(YearStart(Today(), -1));

LET vEndDate = Num(YearEnd(Today()));

FiscalCalendar:

LOAD

*,

Dual('Q' & Ceil(FiscalMonth/3), Ceil(FiscalMonth/3)) AS FiscalQuarter, // Fiscal Calendar Quarter

Dual(Text(Date(MonthEnd(Date), 'MMM')), FiscalMonth) AS FiscalMonthName; // Fiscal Calendar Month Name

LOAD

*,

Year(Date) AS Year, // Standard Calendar Year

Month(Date) AS Month,

Week(Date) as Week, // Standard Calendar Month

Date(MonthEnd(Date), 'MMM') AS MonthName,  // Standard Calendar Month Name

Dual('Q' & Ceil(Month(Date)/3), Ceil(Month(Date)/3)) AS Quarter,  // Standard Calendar Quarter

Mod(Month(Date) - $(vFiscalYearStartMonth), 12)+1 AS FiscalMonth,  // Fiscal Calendar Month

YearName(Date, 0, $(vFiscalYearStartMonth)) AS FiscalYear;  // Fiscal Calendar Year

LOAD

Date($(vStartDate) + RangeSum(Peek('RowNum'), 1) - 1) AS Date,

RangeSum(Peek('RowNum'), 1) AS RowNum

AutoGenerate vEndDate - vStartDate + 1;

[#7]

Hi,

Try fetching the Selected month/Year using "GetFieldSelections ()" and the Minimum Month/Year  in Two Variables.

Use these Two Variable to Get the Sum of the required Field.

Hope it Helps!

Cheers !