Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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)))
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
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.
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.
can you share sample app?
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.
working on server , cannot share the data
Hi Bhawna,
Please try to use this code in your script...
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)