Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello Friends,
I just start fun with qliksense. Can anyone tell me I just download 3.1 SR3 and i've seen default application. Every script ended with below one
[autoCalendar]:
DECLARE FIELD DEFINITION Tagged ('$date')
FIELDS
Dual(Year($1), YearStart($1)) AS [Year] Tagged ('$axis', '$year'),
Dual('Q'&Num(Ceil(Num(Month($1))/3)),Num(Ceil(NUM(Month($1))/3),00)) AS [Quarter] Tagged
('$quarter'),
Dual(Year($1)&'-Q'&Num(Ceil(Num(Month($1))/3)),QuarterStart($1)) AS [YearQuarter] Tagged
('$axis', '$yearquarter'),
Month($1) AS [Month] Tagged ('$month'),
Dual(Year($1)&'-'&Month($1), monthstart($1)) AS [YearMonth] Tagged ('$axis', '$yearmonth'),
Dual('W'&Num(Week($1),00), Num(Week($1),00)) AS [Week] Tagged ('$weeknumber'),
Date(Floor($1)) AS [Date] Tagged ('$date');
DERIVE FIELDS FROM FIELDS "Month" USING [autoCalendar] ;
What is the usage of above one, Is this act as Calendar in qlikview / What? Can you describe more
And if some one have any qlik sense content for guide please share that content with me
Hi anil,
AFAIK
This code about master calender and it is auto generated script when you have a single date field in your data
automatically it will generate the code.
HTH
Chanty
When you say auto generated script, This must default.
But, I've created one sample application and then i have loaded simple excel. But, the code is not visible to me on my application do i need to set any setting to visible default script on qliksense?
am not sure default when you have date field in your data. and there is no option like that i think.
I have date field as well. But, the script is not generated. Anyhow, thanks boss for time
hmmm i hope this is happens in only with 3.0 i think when i was working on that time it is default script. am not sure about latest versions.
Hi Anil
You can create automatic script using Data Manager. This creates an automatic calendar for every date using the derived date feature and auto calendar
If you use Data load editor you can still used derived date. I sometimes use this now with a canonical date as you can do time aware charts. And it automatically creates the calendar (one date for every day between start a dn finish) for the canonical date
Robert,
What if I have multiple fact tables each with dates and I want to have a canonical date so I can filter on it and see values from each table for that date?
Here;s what the data manager loaded:
[Sites]:
LOAD [Site_ID],
[Sitename],
[Mid],
[Add_1],
[Add_2],
[Postcode],
[Latitude],
[Longitude],
[F9],
[F10],
[F11],
[F12],
[F13]
FROM [lib://qlikid_djbrennan/Anon Sites.xlsx]
(ooxml, embedded labels, table is Sites);
[Sheet1_c872b52b-b735-95f5-5de3-e6d41176]:
LOAD
[Site_ID],
[Merchant ID],
[Site],
Date([St_Date] ,'DD/MM/YYYY') AS [StDate],
[Card_Type],
[Auth],
[Turnover],
[Transactions],
[Rate],
[MSC Fee]
FROM [lib://qlikid_djbrennan/Ex_Cards.xlsx]
(ooxml, embedded labels, table is Sheet1);
[Sheet1_23a70457-064d-9065-8a8b-91f5ea5e]:
LOAD
[MPANID],
Date([Date] ,'DD/MM/YYYY') AS [ElecDate],
[TimeSlot],
[Value],
[DayNight],
[WDWE]
FROM [lib://qlikid_djbrennan/Ex_Elec.xlsx]
(ooxml, embedded labels, table is Sheet1);
[Sheet1_be0ddd76-8edf-9f96-e671-c66a1fd1]:
LOAD
[Site_ID],
[Telephone_No],
Date([Date] ,'DD/MM/YYYY') AS [FixedDate],
[Time],
[Dialled_No],
[Description] AS [Sheet1-2.Description],
[Dur_secs],
[Cost (£)],
[Call_Type],
[PPM],
[Focus_Contract],
[Opt_PPM],
[Opt_Cost],
[Saving]
FROM [lib://qlikid_djbrennan/Ex_Fixed.xlsx]
(ooxml, embedded labels, table is Sheet1);
[Sheet1_74ec4ccd-6154-d91d-87e1-d3d9dc5c]:
LOAD
[Invoice],
Date([Invoice Date] ,'DD/MM/YYYY') AS [MobDate],
[Account number],
[Mob_Num],
[Description] AS [Sheet1-3.Description],
[Nature],
[Net_Charge],
[Gross _Charge]
FROM [lib://qlikid_djbrennan/Ex_Mobile.xlsx]
(ooxml, embedded labels, table is Sheet1);
[Curr_el_contracts]:
LOAD [MPANID],
[Company],
[Site_ID],
[Supply],
[ASC/KVA],
[Top line],
[Contract Start],
[Contract End],
[StCharge],
[DayRate],
[NightRate],
[DUOSCh],
[FIT]
FROM [lib://qlikid_djbrennan/Anon Site_elec_Dim.xlsx]
(ooxml, embedded labels, table is Curr_el_contracts);
[Mob_users]:
LOAD [Mob_Num],
[User],
[Site_ID],
,
FROM [lib://qlikid_djbrennan/Anon Mob_Users.xlsx]
(ooxml, embedded labels, table is Mob_users);
RENAME TABLE [Sheet1_c872b52b-b735-95f5-5de3-e6d41176] TO [Cards];
RENAME TABLE [Sheet1_23a70457-064d-9065-8a8b-91f5ea5e] TO [Elec];
RENAME TABLE [Sheet1_be0ddd76-8edf-9f96-e671-c66a1fd1] TO [Fixed];
RENAME TABLE [Sheet1_74ec4ccd-6154-d91d-87e1-d3d9dc5c] TO [Mobile];
[autoCalendar]:
DECLARE FIELD DEFINITION Tagged ('$date')
FIELDS
Dual(Year($1), YearStart($1)) AS [Year] Tagged ('$axis', '$year'),
Dual('Q'&Num(Ceil(Num(Month($1))/3)),Num(Ceil(NUM(Month($1))/3),00)) AS [Quarter] Tagged ('$quarter', '$cyclic'),
Dual(Year($1)&'-Q'&Num(Ceil(Num(Month($1))/3)),QuarterStart($1)) AS [YearQuarter] Tagged ('$yearquarter', '$qualified'),
Dual('Q'&Num(Ceil(Num(Month($1))/3)),QuarterStart($1)) AS [_YearQuarter] Tagged ('$yearquarter', '$hidden', '$simplified'),
Month($1) AS [Month] Tagged ('$month', '$cyclic'),
Dual(Year($1)&'-'&Month($1), monthstart($1)) AS [YearMonth] Tagged ('$axis', '$yearmonth', '$qualified'),
Dual(Month($1), monthstart($1)) AS [_YearMonth] Tagged ('$axis', '$yearmonth', '$simplified', '$hidden'),
Dual('W'&Num(Week($1),00), Num(Week($1),00)) AS [Week] Tagged ('$weeknumber', '$cyclic'),
Date(Floor($1)) AS [Date] Tagged ('$axis', '$date', '$qualified'),
Date(Floor($1), 'D') AS [_Date] Tagged ('$axis', '$date', '$hidden', '$simplified');
DERIVE FIELDS FROM FIELDS [StDate], [ElecDate], [FixedDate], [MobDate], [Contract Start], [Contract End] USING [autoCalendar] ;
Hi denis
What you have got is one calendar for every date. So you can expand the date(s) and filter as you require. But if you use the FixedDate.Month filter you will like get the wrong totals if you want say MobDate
The advantage with the canonical date is you set up one calendar only. And use this one calendar (Day / Month / Year etc) for all dates
But you must create a date bridge between these date
Robert,
Many thanks. I'm struggling with the script. I adapted this from Henric's post on canonical dates but I know I'm missing something.
DateBridge:
Load SiteID, Applymap(‘SiteID2ElecDate’,SiteID,Null()) as CanonicalDate, ‘Elec’ as DateType
Resident HH;
Load SiteID, Applymap(‘SiteID2CardDate',OrderID,Null()) as CanonicalDate, ‘Card’ as DateType
Resident Cards;
Load SiteID, FixedDate as CanonicalDate, ‘Fixed’ as DateType
Resident Fixed;
Load SiteID, MobDate as CanonicalDate, ‘Mob’ as DateType
Resident Mobiles;
I also attach my QVF ( a small set of sample data. Can you point me in the right direction?