Skip to main content
Announcements
Join us at Qlik Connect for 3 magical days of learning, networking,and inspiration! REGISTER TODAY and save!
cancel
Showing results for 
Search instead for 
Did you mean: 
Anil_Babu_Samineni

New to Qliksense - Script

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

Best Anil, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful
13 Replies
Chanty4u
MVP
MVP

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

Anil_Babu_Samineni
Author

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?

Best Anil, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful
Chanty4u
MVP
MVP

am not sure default  when you have date field in your data. and there is no option like that i think.

Anil_Babu_Samineni
Author

I have date field as well. But, the script is not generated. Anyhow, thanks boss for time

Best Anil, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful
Chanty4u
MVP
MVP

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.

robert99
Specialist III
Specialist III

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

Qlik Sense – Date & Time

https://community.qlik.com/blogs/qlikviewdesignblog/2014/02/17/canonical-date?_ga=1.267491272.717680...

What’s new in Qlik Sense 3.0 - Time-aware Charts

temp3.JPG

Not applicable

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] ;

robert99
Specialist III
Specialist III

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

https://community.qlik.com/blogs/qlikviewdesignblog/2014/02/17/canonical-date?_ga=1.267491272.717680...

Not applicable

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?