Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Qlik® Product Spotlight: Discover what’s possible. Get more from our products.
See for yourself. Register today.
junasharma
Contributor

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

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

7 Replies
harishkumarg
Contributor III

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

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

Not applicable

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

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.

junasharma
Contributor

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

Hi Rajesh,

I used Concat(chr(39)  &  distinct Fiscal Year  &  chr(39), ',') in the expression for the year and I am getting error in the expresssion but same expression I used repalcing Fiscal year with Month , it works. Checked all the brackets, comma and spaces.

shraddha_g
Honored Contributor III

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

can you share sample app?

teiswamsler
Contributor II

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

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.

junasharma
Contributor

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

working on server , cannot share the data

kamalqlik
Valued Contributor

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

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)