Rolling Fiscal Year over Year

Add to your relative calendar:


CASE
WHEN (DATEDIFF(DAY, C2.FYStartDate, C.FYStartDate) / 28) BETWEEN -12 AND 0                 Then 'This Year'
WHEN (DATEDIFF(DAY, C2.FYStartDate, C.FYStartDate) / 28) BETWEEN -24 AND -13
Then 'Last Year' ELSE NULL End AS [RollingFiscalYear]

Sort:

Match(Cal.WeekMon
,Dual($(=Only({<FullDate = {'$(=Date(Today()))'}>}Cal.WeekMon)),1)
,Dual($(=Only({<FullDate = {'$(=Date(Today()-7))'}>}Cal.WeekMon)),2)
,Dual($(=Only({<FullDate = {'$(=Date(Today()-14))'}>}Cal.WeekMon)),3)
,Dual($(=Only({<FullDate = {'$(=Date(Today()-21))'}>}Cal.WeekMon)),4)
,Dual($(=Only({<FullDate = {'$(=Date(Today()-28))'}>}Cal.WeekMon)),5)
,Dual($(=Only({<FullDate = {'$(=Date(Today()-35))'}>}Cal.WeekMon)),6)
,Dual($(=Only({<FullDate = {'$(=Date(Today()-42))'}>}Cal.WeekMon)),7)
,Dual($(=Only({<FullDate = {'$(=Date(Today()-49))'}>}Cal.WeekMon)),8)
,Dual($(=Only({<FullDate = {'$(=Date(Today()-56))'}>}Cal.WeekMon)),9)
,Dual($(=Only({<FullDate = {'$(=Date(Today()-63))'}>}Cal.WeekMon)),10)
,Dual($(=Only({<FullDate = {'$(=Date(Today()-70))'}>}Cal.WeekMon)),11)
,Dual($(=Only({<FullDate = {'$(=Date(Today()-77))'}>}Cal.WeekMon)),12)
,Dual($(=Only({<FullDate = {'$(=Date(Today()-84))'}>}Cal.WeekMon)),13)
,Dual($(=Only({<FullDate = {'$(=Date(Today()-91))'}>}Cal.WeekMon)),14)
,Dual($(=Only({<FullDate = {'$(=Date(Today()-98))'}>}Cal.WeekMon)),15)
,Dual($(=Only({<FullDate = {'$(=Date(Today()-105))'}>}Cal.WeekMon)),16)
,Dual($(=Only({<FullDate = {'$(=Date(Today()-112))'}>}Cal.WeekMon)),17)
,Dual($(=Only({<FullDate = {'$(=Date(Today()-119))'}>}Cal.WeekMon)),18)
,Dual($(=Only({<FullDate = {'$(=Date(Today()-126))'}>}Cal.WeekMon)),19)
,Dual($(=Only({<FullDate = {'$(=Date(Today()-133))'}>}Cal.WeekMon)),20)
,Dual($(=Only({<FullDate = {'$(=Date(Today()-140))'}>}Cal.WeekMon)),21)
,Dual($(=Only({<FullDate = {'$(=Date(Today()-147))'}>}Cal.WeekMon)),22)
,Dual($(=Only({<FullDate = {'$(=Date(Today()-154))'}>}Cal.WeekMon)),23)
,Dual($(=Only({<FullDate = {'$(=Date(Today()-161))'}>}Cal.WeekMon)),24)
,Dual($(=Only({<FullDate = {'$(=Date(Today()-168))'}>}Cal.WeekMon)),25)
,Dual($(=Only({<FullDate = {'$(=Date(Today()-175))'}>}Cal.WeekMon)),26)
,Dual($(=Only({<FullDate = {'$(=Date(Today()-182))'}>}Cal.WeekMon)),27)
,Dual($(=Only({<FullDate = {'$(=Date(Today()-189))'}>}Cal.WeekMon)),28)
,Dual($(=Only({<FullDate = {'$(=Date(Today()-196))'}>}Cal.WeekMon)),29)
,Dual($(=Only({<FullDate = {'$(=Date(Today()-203))'}>}Cal.WeekMon)),30)
,Dual($(=Only({<FullDate = {'$(=Date(Today()-210))'}>}Cal.WeekMon)),31)
,Dual($(=Only({<FullDate = {'$(=Date(Today()-217))'}>}Cal.WeekMon)),32)
,Dual($(=Only({<FullDate = {'$(=Date(Today()-224))'}>}Cal.WeekMon)),33)
,Dual($(=Only({<FullDate = {'$(=Date(Today()-231))'}>}Cal.WeekMon)),34)
,Dual($(=Only({<FullDate = {'$(=Date(Today()-238))'}>}Cal.WeekMon)),35)
,Dual($(=Only({<FullDate = {'$(=Date(Today()-245))'}>}Cal.WeekMon)),36)
,Dual($(=Only({<FullDate = {'$(=Date(Today()-252))'}>}Cal.WeekMon)),37)
,Dual($(=Only({<FullDate = {'$(=Date(Today()-259))'}>}Cal.WeekMon)),38)
,Dual($(=Only({<FullDate = {'$(=Date(Today()-266))'}>}Cal.WeekMon)),39)
,Dual($(=Only({<FullDate = {'$(=Date(Today()-273))'}>}Cal.WeekMon)),40)
,Dual($(=Only({<FullDate = {'$(=Date(Today()-280))'}>}Cal.WeekMon)),41)
,Dual($(=Only({<FullDate = {'$(=Date(Today()-287))'}>}Cal.WeekMon)),42)
,Dual($(=Only({<FullDate = {'$(=Date(Today()-294))'}>}Cal.WeekMon)),43)
,Dual($(=Only({<FullDate = {'$(=Date(Today()-301))'}>}Cal.WeekMon)),44)
,Dual($(=Only({<FullDate = {'$(=Date(Today()-308))'}>}Cal.WeekMon)),45)
,Dual($(=Only({<FullDate = {'$(=Date(Today()-315))'}>}Cal.WeekMon)),46)
,Dual($(=Only({<FullDate = {'$(=Date(Today()-322))'}>}Cal.WeekMon)),47)
,Dual($(=Only({<FullDate = {'$(=Date(Today()-329))'}>}Cal.WeekMon)),48)
,Dual($(=Only({<FullDate = {'$(=Date(Today()-336))'}>}Cal.WeekMon)),49)
,Dual($(=Only({<FullDate = {'$(=Date(Today()-343))'}>}Cal.WeekMon)),50)
,Dual($(=Only({<FullDate = {'$(=Date(Today()-350))'}>}Cal.WeekMon)),51)
,Dual($(=Only({<FullDate = {'$(=Date(Today()-357))'}>}Cal.WeekMon)),52)
)

Peek on load variable

Getting full fiscal quarters via their start date:


Start:
LOAD Distinct
Date(Floor(FYStartDate),'YYYY-MM-DD') AS FYStartDate
;
SQL
Select Distinct
Min(FYStartDate) AS FYStartDate
From REF.Calendar
Where Year(FullDate) = Year(DATEADD(mm,-12,GETDATE()))
AND FYQuarter = '1'
;

LET vStartDate = peek('FYStartDate');

Drop Table Start;

Current Week OnOpen Macro

so I don't forget...


Sub OnOpen

InitialSelections

End Sub

Sub InitialSelections
ActiveDocument.Fields("Cal.RelativeWeek").select "0",True
ActiveDocument.Fields("Cal.WeekStartMon").selectpossible
ActiveDocument.Fields("Cal.RelativeWeek").clear
End Sub

Syntax for Alternate States:

ActiveDocument.GetField("CUSTOMERS", "State1")

GeoAnalytics points...

City state is "Location Named Point"

'[-118.243685,34.052234]' is "Point"

Long Lat in separate fields is "Latitude and Longitude Point"

NPrinting Exporting Numbers as Text...grrrrr

At first, I was   (╯°□°）╯︵ ┻━┻   and   ლ(｀ー´ლ)  and   ಥ_ಥ  and just about became a
 ““”̿ ̿ ̿ ̿ ̿’̿’̵͇̿̿з=(•̪●)=ε/̵͇̿̿/̿ ̿ ̿ ̿ ̿’““  ! No matter what I did, NPrinting would export my darn tables as text! If it could only translate the values to numbers like I can translate text emoticons into feelings.

calculating...calculating...calculating...calculating...calculating...calculating...

At first, I was flipping tables and angry face and sad and just about became a crazed gunner! Then, I found and adapted an Excel macro that gave me a ヽ(´▽`)/  .

Days : Hours : Minutes

The Qlik Community comes through again!

I need to turn a buck of minutes into a Days : Hours : Minutes format.

It was as simple as:

=Interval(Interval#([your minutes],'m'),'d:hh:mm')

New Beginner Course on PluralSight!

Go watch my new PluralSight course!

http://www.pluralsight.com/courses/qlikview-create-data-discovery-tool

It's intended for the absolute beginner, so keep that in mind when you send me your accolades!

And for those that come here for the jokes....

When my wife told me to stop impersonating a flamingo I had to put my foot down.