New to Qliksense - Script

[Anil_Babu_Samineni]

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

[Chanty4u]

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]

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?

[Chanty4u]

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

[Anil_Babu_Samineni]

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

[Chanty4u]

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]

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

[#8]

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]

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...

[#10]

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?