14 Replies Latest reply: Aug 31, 2015 3:35 PM by Marcus Sommer RSS

    Time Period using Master calender

      Hi community,

       

      I have table like

      Invoices:

       

      Invoice num, Amount, approval date

      333, 500, 1/7/2014

      124,  600, 1/6/2014

      125, 700, 1/7/2014

      126, 800, 1/8/2014

      123, 5004, 1/9/2014

      124,  6200, 1/10/2014

      125, 7200, 1/11/2014

      126, 2800, 1/12/2014

      123, 500, 1/1/2015

      124,  600, 1/2/2015

      125, 700, 1/3/2015

      126, 800, 1/4/2015

      123, 500, 1/5/2015

      124,  600, 1/6/2015

      125, 700, 1/7/2015

       

       

      like this i have data with three columns,

      no my requirement is I have to get the months in filter  like "last 12 months", "last 6 months", "last 3 months" and

      for the current FY i need "Current Year" and for previous 12 months i need to show  as "Previous Year"instead of "FY Q1", "FY Q2", "FY Q3" , "FY Q4"

      when i reload with new month's data selection should also change to from current month to as per selection eg: when i select last 3 months i should get the data of august, july, june.

       

      Overall i need to create column in script with

      TimePeriod

      Current year,

      last 12 months,

      last 6 months,

      last 3 months,

      Previous year

       

      thanks

      Reddy

        • Re: Time Period using Master calender
          Harshit Bhatia

          Hi,

          You will have to use the concept of Dollar Expansion in dimension tab along with some macros coding.

           

          For this you will have to create data islands as follows:

          Capture.PNG

           

           

           

          Code this as an an expression in list box: Range.$(Value_form_Type). this way the list box will be dynamic and will show values for Range.Year, Range.Month, Range.Week in a single list box as per the value selected on Type

           

          So when Someone selects Year the Range.$(Value_form_Type) list box is showing the value for Year.

          Now for each of the values write a Macro so for Range.Year when selected value is Current the macro will select the Current Year from the master calender. and so on you may write Macros for the others too

          • Re: Time Period using Master calender
            Harshit Bhatia

            Hi,

            Yes you have to do this in script only.

            You have to create the data island in script, but to see them on the UI you have to create the list boxes. Again when someone select the values in these list boxes you have to write macros for that, which will make your automatic selections on Master calendar.

            Let me know if you need more clarification on this.

              • Re: Time Period using Master calender

                Yes Harshith,

                some more clarification needed and  am working on qlik sense.

                result should be as shown in screenshot above added.

                thanks

                  • Re: Time Period using Master calender
                    Harshit Bhatia

                    Hi Prashanth,

                    what you request we did exactly in QlikView so i suppose this would work in Sense too.

                     

                    for QlikView Scripting:

                    Create these data island:

                    DateType:

                    LOAD * INLINE [

                        Type

                        Year

                        Month

                        Week

                        Custom

                    ];

                     

                     

                    YearType:

                    LOAD * INLINE [

                        Range.Year

                        Current

                        Previous

                       

                    ];

                     

                     

                    MonthType:

                    LOAD * INLINE [

                        Range.Month

                        3 Months

                        6 Months

                        12 Months

                    ];

                     

                     

                    WeekType:

                    LOAD * INLINE [

                        Range.Week

                        1 Week

                        2 Week

                        4 Week

                    ];

                     

                    Now create Two List boxes, One with Type and another with the expression as follows: =$(='Range.'&vType)

                    where vType is a variable defined in UI as

                    vType==GetFieldSelections(Type)

                     

                    as you make selections list box Type your expression list box will change dynamically and list values as per selection ie. for year, months 7 weeks

                     

                    the next part is Macros : which you may write in VB for what ever value is selected in Range.(Type) your macro will run and select the date range from Master calender accordingly

                • Re: Time Period using Master calender
                  Marcus Sommer

                  I suggest to create flags for this within the master-calendar: Calendar with period flags. More informations will you find here: How to use - Master-Calendar and Date-Values

                   

                  - Marcus

                    • Re: Time Period using Master calender

                      Hi marcus,

                      i need to create a dimension (eg: time period)

                      When i use it in filter pane : it should should me options like:

                      Time Period (values in the order below)

                      Last 12 Months

                      Last 6 Months

                      Last 3 Months

                      Current Fiscal YTD

                      Previous Fiscal YTD

                      Previous 12 Months

                      FY16

                      FY15

                      FY14

                      FY13

                       

                      if i select 12months , i should get the data from Aug15 to sep14

                       

                      thanks

                      Prashanth

                        • Re: Time Period using Master calender
                          Marcus Sommer

                          You couldn't create this flagging only in the script and could choose them within a single listbox without to adjust expressions in the gui. Your time-periods eliminate eachother within one field.

                           

                          The nearest way to reach your requirements is to create for each time-period an own flag, create a small inline-table with your time-periods names and using from a variable which returned the flag of the choosen period:

                           

                          variable: vFlag

                          = pick(match(getfieldselections([Time Periods], 'Last 12 Months', ...), $(vFlagLast12M), ....)

                           

                          expression:

                          = sum(value * $(vFlag))

                           

                          or vFlag would be used within a selection-trigger to select the flag-field:

                           

                          action-field:

                          $(vFlag)

                           

                          action-value:

                          = 1

                           

                          - Marcus

                            • Re: Time Period using Master calender

                              Hi marcus,

                              My requirement is Qlik sense based.

                              i have to do it in back end only... is there  any other way.

                               

                              Thank you.

                                • Re: Time Period using Master calender
                                  Andy Weir

                                  mMarcus idea will work in SEnse if ŷou use the qsvariable extension to let the user set your date period selection.  It even have a drop down control for your different selections.

                                   

                                  REgards

                                   

                                   

                                  Andy

                                  • Re: Time Period using Master calender
                                    Rob Wunderlich

                                    This example shows how to create temporal date groups in script.

                                    Qlikview Cookbook: Date Grouping http://qlikviewcookbook.com/recipes/download-info/date-grouping/

                                     

                                    I know you are asking about Sense. so here it the script which will work in Sense as well.

                                     

                                    // Assign current date to a variable. Makes INLINE below a little neater and ensures we don't cross midnight boundary.

                                    LET vToday=today(1);

                                     

                                    Ranges:

                                    // Use the "evaluate()" function to execute the input function text and convert it to actual date values.

                                    LOAD Range, date(evaluate(RangeStart)) as RangeStart, date(evaluate(RangeEnd)) as RangeEnd

                                    ;

                                    /* The INLINE data defines the Range Name, Starting date of the Range, and Ending date of the Range.

                                    The Start/End values are defined using standard QV date functions.

                                    Semicolon is used for a field delimeter because some of the functions contain commas.

                                    */

                                    LOAD * INLINE [

                                    Range; RangeStart; RangeEnd

                                    Today; vToday; DayEnd(vToday)

                                    Yesterday; vToday-1; DayEnd(vToday-1)

                                    This Week; WeekStart(vToday); WeekEnd(vToday)

                                    Last Week; WeekStart(vToday,-1); WeekEnd(vToday, -1)

                                    Last 2 Weeks; WeekStart(today(1),-1); WeekEnd(today(1))

                                    This Month; MonthStart(vToday); MonthEnd(vToday)

                                    Last Month; MonthStart(vToday,-1); MonthEnd(vToday,-1)

                                    Last Two Months; MonthStart(vToday,-1); MonthEnd(vToday)

                                    This Year; YearStart(vToday); YearEnd(vToday)

                                    Last Year; YearStart(vToday,-1); YearEnd(vToday,-1)

                                    ] (delimiter is ';')

                                    ;

                                     

                                    /*

                                    Use IntervalMatch to link the Date field into multiple Range fields.

                                    Doing a JOIN avoids creating a messy synthetic key.

                                    */

                                    JOIN (Ranges) IntervalMatch (Date) LOAD RangeStart, RangeEnd RESIDENT Ranges;

                                    // Because we did a JOIN, we may drop the the Start/End fields.

                                    DROP FIELDS RangeStart, RangeEnd;

                                  • Re: Time Period using Master calender
                                    Marcus Sommer

                                    I'm not sure if nowadays variables could be used within sense. But generally it would work without them - it's only a bit simplifying and neater to use variables to keep the expression clean. Everything else are normal expressions and should work in sense, too.

                                     

                                    But the suggestion from Rob looked very interesting - I would try it.

                                     

                                    - Marcus