topic Re: Complex Date in QlikView

Complex Date

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

HI ALl

sum({ $<[Fiscal Year] ={$(=vFisCurrYear)},[Fiscal Month ]={$(=vFisPrevMonth)},Month_DG=>}Cost

This expression is for current year previous month ..

And my fiscal month starts from April as 1st month

When i select June this expression will display May releated data ( Previous MOnth)

and when i select April this has to display the data for March( which is of previous Year) ..

how can i include this condition in this Expression ...

Below is the content within the field

vFisPrevMonth=Max([Fiscal Month])-1

[Fiscal Year] = 2016,2017,2018

[Fiscal Month]= 1,2,3,4,....so on

vFisCurrYear= =MAX([Fiscal Year])

Can please someone suggest me on this ?

Thanks in advance

Re: Complex Date

Anil_Babu_Samineni — Thu, 07 Sep 2017 11:07:33 GMT

If you selecting April from Fiscal Month what you are getting in text object using this expression

=Max(FiscalYear)

=Max(FiscalYear)-1

Re: Complex Date

smilingjohn — Thu, 07 Sep 2017 11:12:02 GMT

=Max(FiscalYear) is Giving me 2018

and Max(FiscalYear)-1 is giving me 2017

and For max(Fiscal Month) its giving 1

for max(Fiscal Month) its giving 0

Re: Complex Date

Thu, 07 Sep 2017 11:14:22 GMT

HI john,

what you want exactly give sample output?

Re: Complex Date

smilingjohn — Thu, 07 Sep 2017 11:18:17 GMT

Hi Thirumal ..

one will give current month data

one more column will give the prvious month data ..

And for previous month data i have shared the expression ..

sum({ $<[Fiscal Year] ={$(=vFisCurrYear)},[Fiscal Month ]={$(=vFisPrevMonth)},Month_DG=>}Cost

NOw the fiscal month starts from April

When i select April ..the Previous month Column should display the data for March( which will become previous years)

I wanted to include this requirement in the given expression

Re: Complex Date

sunny_talwar — Thu, 07 Sep 2017 11:28:15 GMT

I suggest creating a new field in the script called MonthYear

LOAD Date,

...,

Date(MonthStart(Date), 'MM-YYYY') as MonthYear

FROM ...;

And then this

Sum({$<[Fiscal Year], [Fiscal Month], Month_DG, MonthYear = {"$(=Date(AddMonths(Max(MonthYear), -1), 'MM-YYYY'))"}>} Cost)

Re: Complex Date

smilingjohn — Thu, 07 Sep 2017 11:36:58 GMT

HI Sunny Thanks for the reply

Cant we do it in the existing scripture by applyinf any if condition ?

like if Current month is 1 the this expression something like that .

Becuase already there is a master calendar implemented in this ..

Please provide any suggestion

Re: Complex Date

sunny_talwar — Thu, 07 Sep 2017 11:40:35 GMT

You can, but you are bringing in unnecessary complexities. You mentioned that you already have a mastercalendar... can you share the script it uses? because you might already have a monthyear field which we might be able to use in this case

Re: Complex Date

smilingjohn — Thu, 07 Sep 2017 11:46:09 GMT

Hi Sunny

Below is the master calendar being used in this script ...

LET vMinDate = num(MakeDate(2016, 1));

LET vMaxDate = num(YearEnd(Today()));

SET vFiscalMonthStart = 7; //e.g. use 4 if Fiscal Start is April

LET vFiscalMonthEnd = if(vFiscalMonthStart = 1, 12, vFiscalMonthStart - 1);

Directory; //Current directory

//------------------- Temporary Calendar -------------------

TempCalendar:

LOAD

$(vMinDate)+IterNo()-1 AS Num_MIS,

Date($(vMinDate)+IterNo()-1,'MM/DD/YYYY') AS TempDate_MIS

AUTOGENERATE 1 WHILE ($(vMinDate)+IterNo()-1) <= $(vMaxDate);

//----------------------------------------------------------

FWMasterCalendar:

LOAD

TempDate_MIS, //04/29/2007

TempDate_MIS AS [END_DATE_MIS], //04/29/2007

MonthStart(TempDate_MIS) AS [Month Start Date_MIS], //04/01/2007

MonthEnd(TempDate_MIS) AS [Month End Date_MIS], //04/30/2007

//Year

Year(TempDate_MIS) AS Year_MIS, //2007

'Year' AS [Full Year_MIS], //Year

Right(YearName(TempDate_MIS,0,$(#vFiscalMonthStart)),4) AS [Fiscal Year_MIS], //2008 //If($(#vFiscalMonthEnd) < 12 and Month(TempDate_MIS) >= $(#vFiscalMonthStart), Year(TempDate_MIS)+1, Year(TempDate_MIS)) AS FYear,

YearName(TempDate_MIS, 0, $(#vFiscalMonthStart)) as [Fiscal Year Name_MIS], //2007-2008

//Semester

'H' & If(Month(TempDate_MIS) < 7, 1, 2) AS [Semester_MIS], //H1

'H' & If(If(Num(Month(TempDate_MIS))>$(#vFiscalMonthEnd), Num(Month(TempDate_MIS))-$(vFiscalMonthEnd), Num(Month(TempDate_MIS)) + (12 - $(vFiscalMonthEnd))) < 7, 1,

2) AS [Fiscal Semester_MIS], //H1

'H' & If(Month(TempDate_MIS) < 7, 1, 2) & ' (' & Year(TempDate_MIS) & ')' AS [Semester Period_MIS], //H1 (2007)

'H' & If(If(Num(Month(TempDate_MIS))>$(#vFiscalMonthEnd), Num(Month(TempDate_MIS))-$(vFiscalMonthEnd), Num(Month(TempDate_MIS)) + (12 - $(vFiscalMonthEnd))) < 7, 1, 2)

& ' (' & Mid(YearName(TempDate_MIS, 0, $(#vFiscalMonthStart)), 3, 2) & '-' & Mid(YearName(TempDate_MIS, 0, $(#vFiscalMonthStart)), 8, 2) & ')' AS [Fiscal Semester Period_MIS], //H1 (07-08)

//Quarter

'Q' & Alt(If(Month(TempDate_MIS)<4,1),if(Month(TempDate_MIS)<7,2),if(Month(TempDate_MIS)<10,3), 4) AS Quarter_MIS, //Q2

'Q' & Alt(If(If(Num(Month(TempDate_MIS))>$(#vFiscalMonthEnd), Num(Month(TempDate_MIS))-$(vFiscalMonthEnd), Num(Month(TempDate_MIS)) + (12 - $(vFiscalMonthEnd))) < 4, 1,

If(If(Num(Month(TempDate_MIS))>$(#vFiscalMonthEnd), Num(Month(TempDate_MIS))-$(vFiscalMonthEnd), Num(Month(TempDate_MIS)) + (12 - $(vFiscalMonthEnd))) < 7, 2,

If(If(Num(Month(TempDate_MIS))>$(#vFiscalMonthEnd), Num(Month(TempDate_MIS))-$(vFiscalMonthEnd), Num(Month(TempDate_MIS)) + (12 - $(vFiscalMonthEnd))) < 10, 3,

4)))) as [Fiscal Quarter_MIS], //Q1

QuarterName(TempDate_MIS) as [Quarter Name_MIS], //Apr-Jun 2007

QuarterName(TempDate_MIS, 0, $(vFiscalMonthStart)) as [Fiscal Quarter Name_MIS], //Apr-Jun 2007

'Q' & Alt(If(Month(TempDate_MIS)<4,1),if(Month(TempDate_MIS)<7,2),if(Month(TempDate_MIS)<10,3), 4) & ' (' & Mid(QuarterName(TempDate_MIS), 1, 7) & ')' as [Quarter Months_MIS], //Q2 (Apr-Jun)

'Q' & Alt(If(If(Num(Month(TempDate_MIS))>$(#vFiscalMonthEnd), Num(Month(TempDate_MIS))-$(vFiscalMonthEnd), Num(Month(TempDate_MIS)) + (12 - $(vFiscalMonthEnd))) < 4, 1,

If(If(Num(Month(TempDate_MIS))>$(#vFiscalMonthEnd), Num(Month(TempDate_MIS))-$(vFiscalMonthEnd), Num(Month(TempDate_MIS)) + (12 - $(vFiscalMonthEnd))) < 7, 2,

If(If(Num(Month(TempDate_MIS))>$(#vFiscalMonthEnd), Num(Month(TempDate_MIS))-$(vFiscalMonthEnd), Num(Month(TempDate_MIS)) + (12 - $(vFiscalMonthEnd))) < 10, 3,

4)))) & ' (' &

Mid(QuarterName(TempDate_MIS, 0, $(vFiscalMonthStart)), 1, 7) & ')' as [Fiscal Quarter Months_MIS], //Q1 (Apr-Jun)

Alt(If(Month(TempDate_MIS)<4,1),if(Month(TempDate_MIS)<7,2),if(Month(TempDate_MIS)<10,3), 4) AS [Quarter No_MIS], //2

Alt(If(If(Num(Month(TempDate_MIS))>$(#vFiscalMonthEnd), Num(Month(TempDate_MIS))-$(vFiscalMonthEnd), Num(Month(TempDate_MIS)) + (12 - $(vFiscalMonthEnd))) < 4, 1,

If(If(Num(Month(TempDate_MIS))>$(#vFiscalMonthEnd), Num(Month(TempDate_MIS))-$(vFiscalMonthEnd), Num(Month(TempDate_MIS)) + (12 - $(vFiscalMonthEnd))) < 7, 2,

If(If(Num(Month(TempDate_MIS))>$(#vFiscalMonthEnd), Num(Month(TempDate_MIS))-$(vFiscalMonthEnd), Num(Month(TempDate_MIS)) + (12 - $(vFiscalMonthEnd))) < 10, 3,

4)))) as [Fiscal Quarter No_MIS], //1

'Q' & Alt(If(Month(TempDate_MIS)<4,1),if(Month(TempDate_MIS)<7,2),if(Month(TempDate_MIS)<10,3), 4)

& ' ' & Year(TempDate_MIS) AS [Quarter Period_MIS], //Q2 2007

'Q' & Alt(If(If(Num(Month(TempDate_MIS))>$(#vFiscalMonthEnd), Num(Month(TempDate_MIS))-$(vFiscalMonthEnd), Num(Month(TempDate_MIS)) + (12 - $(vFiscalMonthEnd))) < 4, 1,

If(If(Num(Month(TempDate_MIS))>$(#vFiscalMonthEnd), Num(Month(TempDate_MIS))-$(vFiscalMonthEnd), Num(Month(TempDate_MIS)) + (12 - $(vFiscalMonthEnd))) < 7, 2,

If(If(Num(Month(TempDate_MIS))>$(#vFiscalMonthEnd), Num(Month(TempDate_MIS))-$(vFiscalMonthEnd), Num(Month(TempDate_MIS)) + (12 - $(vFiscalMonthEnd))) < 10, 3,

4))))

& ' (' & Mid(YearName(TempDate_MIS, 0, $(#vFiscalMonthStart)), 3, 2) & '-' & Mid(YearName(TempDate_MIS, 0, $(#vFiscalMonthStart)), 8, 2) & ')' AS [Fiscal Quarter Period_MIS], //Q1 (07-08)

//Month

Num(Month(TempDate_MIS)) as [Month No_MIS], //4

If(Num(Month(TempDate_MIS))>$(#vFiscalMonthEnd), Num(Month(TempDate_MIS))-$(vFiscalMonthEnd), Num(Month(TempDate_MIS)) + (12 - $(vFiscalMonthEnd))) AS [Fiscal Month No_MIS], //1

Month(TempDate_MIS) as Month_MIS, //Apr

Mid(Date(MonthStart(TempDate_MIS),'YYDDMMMM'), 5) as [Month Name_MIS], //April

Date(MonthStart(TempDate_MIS),'MMM-YY') AS [Month Period_MIS],//Apr-07 //GL Period

//Week

Weekday(TempDate_MIS) AS Weekday_MIS, //Sun

WeekName(TempDate_MIS, 0, 0) as [Week Period_MIS], //2007/17

'W' & Week(TempDate_MIS) AS [Week Name_MIS], //W17

Week(TempDate_MIS) AS [Week No_MIS], //17

//Week Numbers within Month (Week Starting Monday): Current Week No - MonthStart Week No, if < 0 then (Previous Week No) - Month Start Week No + 1)

Date(MonthStart(TempDate_MIS),'MMM-YY') & ' W' &

If( ( Week(TempDate_MIS) - Week(MonthStart(TempDate_MIS)) + 1 ) < 0,

( Week(WeekEnd(TempDate_MIS, -1 * (Week(TempDate_MIS)))) + (Week(TempDate_MIS)) - Week(MonthStart(TempDate_MIS)) + 1 ),

( Week(TempDate_MIS) - Week(MonthStart(TempDate_MIS)) + 1 ) ) as [Week No. Month_MIS], //Apr-07 W5

'W' & Week(Weekend(TempDate_MIS,0,-1) ) as [Week Name Sun Start_MIS], //W18

Week(Weekend(TempDate_MIS,0,-1) ) as [Week No Sun Start_MIS], //18

Date(MonthStart(TempDate_MIS),'MMM-YY') & ' W' &

If( ( Week(Weekend(TempDate_MIS,0,-1)) - Week(Weekend(MonthStart(TempDate_MIS),0,-1)) + 1 ) < 0,

( Week(WeekEnd(TempDate_MIS, -1 * (Week(Weekend(TempDate_MIS,0,-1))),-1)) + (Week(Weekend(TempDate_MIS,0,-1))) - Week(Weekend(MonthStart(TempDate_MIS),0,-1)) + 1 ),

( Week(Weekend(TempDate_MIS,0,-1)) - Week(Weekend(MonthStart(TempDate_MIS),0,-1)) + 1 ) ) as [Week No. Sun Start Month_MIS], //Apr-07 W5

//Day

DayNumberOfYear(TempDate_MIS) as [Day Number of Year_MIS], //120

DayNumberOfYear(TempDate_MIS, 4) as [Fiscal Day Number of Year_MIS], //29

Day(TempDate_MIS) AS [Day No_MIS], //29

If (Day(TempDate_MIS) < 4 OR Day(TempDate_MIS) > 20, (If(Right(Day(TempDate_MIS),1)=1, day(TempDate_MIS) & 'st',(If(Right(Day(TempDate_MIS),1)=2, day(TempDate_MIS) & 'nd',(If(Right(Day(TempDate_MIS),1)=3, Day(TempDate_MIS) & 'rd', Day(TempDate_MIS) & 'th')))))), Day(TempDate_MIS) & 'th') AS [Day th_MIS], //29th

RowNo() AS RowNo_MIS

RESIDENT TempCalendar

ORDER BY TempDate_MIS ASC;

STORE FWMasterCalendar INTO [..\data\FWMasterCalendar.qvd];

Drop Table TempCalendar ;

Re: Complex Date

smilingjohn — Thu, 07 Sep 2017 11:48:26 GMT

Sunny As i mentioned this expresion are used in the straight table and are giving the correct result ..

Only the problem comes where they select the First month of the Fiscaly year and the prvious month will show zero value..

Therefore i want to use something based on condition when 1st month is slected it should show the prvious month ( that beacomes the 12month of the last year) proper data

I hope you got sunny what i want to say

Re: Complex Date

sunny_talwar — Thu, 07 Sep 2017 11:51:11 GMT

Can you try this

Sum({$<[Fiscal Year], [Fiscal Month], Month_DG, [Month Start Date_MIS] = {"$(=Date(AddMonths(Max([Month Start Date_MIS]), -1)))"}>} Cost)

Re: Complex Date

smilingjohn — Thu, 07 Sep 2017 12:07:24 GMT

Hi Sunny

THis is working if i select the month when i select pertivular year this is not working

Is there anything needs to be included in this ?

Re: Complex Date

sunny_talwar — Thu, 07 Sep 2017 12:09:26 GMT

Really? I would have thought it would work... can you share a sample where this isn't working?

Re: Complex Date

smilingjohn — Thu, 07 Sep 2017 12:18:55 GMT

Hi sunny

This is what iam using in expression

=Sum({$<[Fiscal Year_MIS], [Fiscal Month No_MIS], Month_MIS, [Month Start Date_MIS] = {"$(=Date(AddMonths(Max([Month Start Date_MIS]), -1)))"}>} Amount_SBU)

When i select the Fiscal month FIlter Which is = Month_MIS this is will work

and when i select the fiscal year from the filter =[Fiscal Year Name_MIS] = ( which has year like this ...2016-2017 2017-2018)

when i make this selection the data show zero value..

Do we need to include this in the expression ?

Re: Complex Date

sunny_talwar — Thu, 07 Sep 2017 12:21:47 GMT

Ignore that in your expression

=Sum({$<[Fiscal Year_MIS], [Fiscal Month No_MIS], [Fiscal Year Name_MIS], Month_MIS, [Month Start Date_MIS] = {"$(=Date(AddMonths(Max([Month Start Date_MIS]), -1)))"}>} Amount_SBU)

Re: Complex Date

smilingjohn — Thu, 07 Sep 2017 12:32:08 GMT

Thanks Sunny

You always Rock

Thanks a lot bro.....