topic Re: Rooling Totals in QlikView

Rooling Totals

abc_18 — Wed, 25 Nov 2020 16:16:04 GMT

Hi,

I want to calculate one column(FY'15 Actual) based on this condition.

if(Division='PUMA',sum(H2:S2),sum(E2:P2)) as FY'15 Actual.

Please find details in attachments.

Re: Rooling Totals

abc_18 — Thu, 03 Mar 2016 16:45:33 GMT

I am using this calendar script in my application.

Set vFM = 10 ; // First month of fiscal year(Fiscal year starts from OCT-SEPT)

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

MasterCalendar:

Load distinct

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

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

Dual('Q' & Ceil(fMonth/3), Ceil(fMonth/3)) as FQuarter,

Ceil((Date-StartOfFWeekOne+1)/7) as FWeekNo,

Load Year + If(Month>=$(vFM), 1, 0) as fYear, // Numeric fiscal year

Mod(Month-$(vFM), 12)+1 as fMonth, // Numeric fiscal month

Dual('Q' & Ceil(Month/3), Ceil(Month/3)) as Quarter,

WeekStart(FYearStart,0,$(vFD)) as StartOfFWeekOne,

Load

Date,

YearStart(Date,0,$(vFM)) as FYearStart,

Year(Date) as Year,

Month(Date) as Month,

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

Week(Date) as ISOWeekNo,

Dual(WeekDay(Date),Mod(WeekDay(Date-$(vFD)),7)+1) as WeekDay,

Day(Date) as Day,

Date(Date, 'MM/DD') as DATEMMDD

Resident Result;

so if Division is 'PUMA', then value need to calculate based on year ie' Jan-15 to Dec-15 else for other division

calculation is based on fiscal year(Oct-14 to Sep-15).

only for PUMA division we are following this year format(Jan-15 to Dec-15).

Please suggest,

Re: Rooling Totals

sunny_talwar — Thu, 03 Mar 2016 17:55:44 GMT

Are you loading the data using CrossTable Load?

Re: Rooling Totals

abc_18 — Thu, 03 Mar 2016 18:20:23 GMT

Hi,

yes, I am loading data using cross table load.

This is the script.

Temp:

CrossTable(Date, Value, 15)

LOAD *

FROM

(ooxml, embedded labels, table is Working);

Result:

LOAD *, Date(num#(Date),'MMM-YY') as Date2,

text([Cost Code]) AS Code

Resident Temp;

drop table Temp;

drop field Date;

rename Field Date2 to Date;

Re: Rooling Totals

Anonymous — Fri, 04 Mar 2016 06:08:34 GMT

may be helpful for you?

Set Analysis for Rolling Periods

Set Analysis for certain Point in Time

Re: Rooling Totals

abc_18 — Fri, 04 Mar 2016 07:32:22 GMT

Hi Balraj,

Thanks for your reply, that's a useful post.

but my requirement is like if I select year 2015 and division puma then value should come from Jan 15-Dec-15

else , from Fiscal year ie; Oct-14-Sept-15 .

how to implement that logic in Expression.