7 Replies Latest reply: Nov 4, 2016 4:58 PM by Kamal Naithani RSS

    How can  we create dynamic expression for Last 4 quarter End and last 6 month of data?

    Juna Sharma

      Hi All,

      I have two dimensions Fiscal Year and Month and two  measure total inventory cost and total quantity amount.Fiscal year starts from July and ends in June. I am creating a line chart and  0  is October 2017. How can I write the expression so that it works for any month/year selected? Right now it only works for 2016 and 2017.

      Last 6 month

      if('$(vKPI_Time_1)' =2,

      if('$(vKPIToggleButton01)'=1,

             If(GetSelectedCount([Month])=1,

                sum(total inventory cost),

                Sum({<calendar_current_month_offset={'0','1','2','3','4','-1'}>}total inventory cost)),

           

              if('$(vKPIToggleButton01)'=2,

                If(GetSelectedCount([Month])=1,

                  sum(total quantity amount),

                  Sum({<calendar_current_month_offset={'0','1','2','3','4','-1'}>}total quantity amount)))

            ),

       

      Last 4 quarter End

        if('$(vKPI_Time_1)' =3, //"Last 4 quarters" Selection

           if('$(vKPIToggleButton01)'=1, //"Value ($)" Selection

              If(GetSelectedCount([Month])=1, //1 period selected

                sum(total inventory cost),

                Sum({<calendar_current_month_offset={'4','7','10','13','0'}>}total inventory cost)),

           

              if('$(vKPIToggleButton01)'=2,

                If(GetSelectedCount([Month])=1,

                  sum(total quantity amount),

                  Sum({<calendar_current_month_offset={'4','7','10','13','0'}>}total quantity amount)))

        • Re: How can  we create dynamic expression for Last 4 quarter End and last 6 month of data?
          HarishKumar Gangadharan

          May be you can try this for last six months data,

           

          DATE={">=$(=MonthStart(Max(DATE),-5))<=$(=Max(DATE))"}


          I would like to know more about your quarter based requirement.



          Regards

          Harish

            • Re: How can  we create dynamic expression for Last 4 quarter End and last 6 month of data?
              RAjesh Bhardwaj

              Hi Juna

               

              I would recommend using the CONCAT function in your expression as a variable. The CONCAT function will list all of your years or months distinctly. This can then be used and stored in a variable. The stored variable can then be used in your expression.

               

              You can dynamically select the months or years and the measures in your expression will calcualte accordingly. It all boils down to how you have scripted your data model in relation to your calendar and the data.

               

              Below is an example.

              1. First create the distinct list of months and years.

              For textual month names use the below expression for your variable.

               

              CONCAT(CHR(39) & DISTINCT MonthName & CHR(39), ',')
              

               

              This will create a string list like: 'Jan', 'Feb', 'Mar' and so on

              When you make a selection for a single month or multiple months, this list will change to whatever is being selected.


              For numerical months use the below expression for you variable


              CONCAT(DISTINCT Month, ',')


              This will create a list like: 1,2,3,4 and so on.


              For the year use the below expression for your variable


              CONCAT(CHR(39) & DISTINCT Year & CHR(39), ',')


              This will create a string list for your year as : '2014', '2015' and so on.


              2. Create the variable to be used in your expression

              Once you have identified and created your expression you should now create your variable.

              Examples: 

              vMonthList = CONCAT(CHR(39) & DISTINCT MonthName & CHR(39), ',')

              vYearList = CONCAT(CHR(39) & DISTINCT Year & CHR(39), ',')


              You will need to identify and establish your quarters to years in your Master Calender in QlikView. I assume you will have a calendar table in your script which has the year, month, date and quarter all mapped out.

               

              Using the above technique of listing your years or months, the associated quarter will be highlighted as it will be in your data.


              3. Create your expression

              Using the variable, you can now include it in your expression for your measures.

               

              Below is an example based on the variables created.

              Measures for year

              SUM({<Year = {'$(vYearList)'>}Sales)


              Measures for Month

               

              SUM({<Month = {'$(vMonthList)@}>Sales)

               

              This can be respectfully used for Quantity measures.

               

              In your chart, when you select a year or multiple years, the chart will show the sales for those selected years. If you used a chart for the quarters then the respectful quarters associated with the selected years will show on the chart.

            • Re: How can  we create dynamic expression for Last 4 quarter End and last 6 month of data?
              Teis thrane Wamsler

              Hi Juna

               

              This expression sum the last 18 Full month based on your Max selected Date.

               

              Sum( {<

              [Date.Field], //Addmore fields to ignore selection made in possible filters in the UI

              [Date.Field] = {">=$(= Monthstart( addmonths( Max([Date.Field]), -18) ) )"}*{"<=$(= Max([Date.Field]) )"}

              >} Sales )

               

              /Teis

               

              !! Use the autoCalender made from "Derive" function in Qlik Sense.

              • Re: How can  we create dynamic expression for Last 4 quarter End and last 6 month of data?
                Kamal Naithani

                Hi Bhawna,

                Please try to use this code in your script...

                 

                 

                FiscalyearData:

                 

                LOAD * Inline

                [

                Date,InvoiceCost

                20120731,12

                20120831,75

                20120930,82

                20121031,83

                20121130,67

                20121231,83

                20130131,90

                20130331,56

                20130430,90

                20130630,658

                20130831,543

                20131031,567

                20131130,67

                20131231,83

                20140131,90

                20140331,56

                20140430,90

                20140630,658

                20140831,543

                20141031,567];

                DateCorrection:

                LOAD *,

                MakeDate(Left(Date,4),mid(Date,5,2),right(Date,2)) as NewDate

                Resident FiscalyearData;

                DROP Table FiscalyearData;

                // We will set the fiscal year to 7 ie Jul

                SET vFiscalYearStartMonth = 7;

                FiscalYearFinalize:

                LOAD *,

                AutoNumber(FiscalYear & FiscalQuarter, 'QuarterID') as [QuarterID];

                LOAD

                *,

                Dual('Q' & Ceil(FiscalMonth/3), Ceil(FiscalMonth/3)) AS FiscalQuarter, // Fiscal Calendar Quarter

                Dual(Text(Date(MonthEnd(NewDate), 'MMM')), FiscalMonth) AS FiscalMonthName,

                FiscalYear&'-'&Dual('Q' & Ceil(FiscalMonth/3), Ceil(FiscalMonth/3)) as FiscalQuarterYear,

                FiscalYear&'-'&Dual(Text(Date(MonthEnd(NewDate), 'MMM')), FiscalMonth) as Monthyear;

                LOAD *,

                YearName(NewDate, 0, $(vFiscalYearStartMonth)) AS FiscalYear_Temp,

                Left(YearName(NewDate, 0, $(vFiscalYearStartMonth)),4) AS FiscalYear,

                Mod(Month(NewDate) - $(vFiscalYearStartMonth), 12)+1 AS FiscalMonth

                Resident DateCorrection;

                DROP Table DateCorrection;

                 

                and use the expression

                Sum({$<QuarterID = {">=$(=Max(QuarterID)-4)<=$(=Max(QuarterID))"},FiscalYear=,FiscalMonthName=,FiscalQuarter>}InvoiceCost)