Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
MonthDates : 1/2015 - 9/2017
expression : sum ( Leads)
Order Year = 2015,2016,2017
Order Month = 1- 12
If MonthDates is my date field do I also need to construct another period ID ?
How do I show rolling 13 months based on current selection ?
Dear Layla,
Can you explain a bit more what do you want to do?
I am unable to attach Qliksense file since it's on company server.
I have a combo bar chart with bars showing values for sum ( Leads) from 1/2015 - 9/ 2017 as MonthDates as Dimension
MonthDates = M/YYYY
I only need to display values from 9/2017 - 9/2016 - Rolling 13 months based on current selection
Just try something
Sum({$<MonthDates={">=1/2015 <= 9/2016"} Leads)
Here is tutorial do help.
Set analysis and set expressions ‒ QlikView
I tried many different expression on this forum and nothing seems to work
Qlikview is not actually reading it as a date. I would think it's reading it as a string.
I think you'd be better off making the MonthDates column show the first day of every month.
1/1/2016,2/1/2016,3/1/2016 etc.
THen qlik will read as a date/integer.
then you could do something like
sum ({$<MonthDates={'>=1/1/2015 <= 9/1/2016'} Leads)
Thanks Wallo - I will try that
What is the expression for rolling 13 based on current selection ?
Meaning any month the user chooses - it has to show rolling 13 months
Try this.
sum({<MonthDates={">=$(=AddMonths(Max(MonthDates),-13))<=$(=Max(MonthDates))"},[Order Year]=,[Order Month]=>}Leads)
Create your own master calendar: (it's always better to build ur own calendar, it's a preference thing)
Just copy and paste this:
Let vMinDate=num(MakeDate(2014,12,31));
//Let vMinDate= AddYears(today(),-2);
Let vMaxDate=Today()+30;
NoConcatenate
[D_Calendar_TMP]:
Load date($(vMinDate)+ RowNo()) as [Calendar date]
AutoGenerate $(vMaxDate)-$(vMinDate);
NoConcatenate
[D_Calendar_TMP2]:
Load RowNo() as [%Date SEQ]
,AutoNumber(date([Calendar date], 'DD/MM/YYYY'),'%Date ID') as [%Date ID]
,date([Calendar date], 'DD/MM/YYYY') as [Calendar date]
,Day([Calendar date]) as [Day of date]
,'W' & right(WeekName([Calendar date],0,-4), 2) as [week date]
,'Q' & Num(ceil(month([Calendar date])/3), '00') as [quarter date]
, right(WeekName([Calendar date],0,-4), 2) as [Week of date]
,Month([Calendar date]) as [Month of date]
,QuarterName([Calendar date]) as [Quarter of date]
,Year([Calendar date]) as [Year of date]
, right(WeekName([Calendar date],0,-4), 2) &'/'&Year([Calendar date]) as [Year week date]
,date(MonthStart([Calendar date]),'MMM YYYY') as [Year month date]
,num( Year([Calendar date]) ) & '- Q' & Num(ceil(month([Calendar date])/3), '00') as [Year quarter date]
,WeekStart([Calendar date],0,-4) as [Week start date]
,WeekEnd([Calendar date],0,-4) as [Week end date]
,MonthStart([Calendar date]) as [Month start date]
,MonthEnd([Calendar date]) as [Month end date]
,QuarterStart([Calendar date]) as [Quarter start date]
,QuarterEnd([Calendar date]) as [Quarter end date]
,YearStart([Calendar date]) as [Year start date]
,YearEnd([Calendar date]) as [Year end date]
,AutoNumber(WeekEnd([Calendar date],0,-4) , '%Year week SEQ') as [%Year week SEQ]
,AutoNumber(Num(Month([Calendar date]), '00')&'/'&Year([Calendar date]), '%Year month SEQ') as [%Year month SEQ]
,AutoNumber(Num(QuarterName([Calendar date]), '00')&'/'&Year([Calendar date]), '%Year quarter SEQ') as [%Year quarter SEQ]
,if(monthstart(date([Calendar date],'MM/YYYY'))= monthstart(date(Today() ,'MM/YYYY')) ,monthstart(date(AddMonths(Today(),-2),'MM/YYYY')), monthstart(date(AddMonths([Calendar date] ,-1),'MM/YYYY'))) as [Month Fact]
Resident D_Calendar_TMP;
join(D_Calendar_TMP2)
Load max([%Date ID]) as [%Date ID]
,1 as [Week end flag]
Resident D_Calendar_TMP2
Group by [Week end date];
join(D_Calendar_TMP2)
Load max([%Date ID]) as [%Date ID]
,1 as [Month end flag]
Resident D_Calendar_TMP2
Group by [Month end date];
join(D_Calendar_TMP2)
Load max([%Date ID]) as [%Date ID]
,1 as [Quarter end flag]
Resident D_Calendar_TMP2
Group by [Quarter end date];
join(D_Calendar_TMP2)
Load max([%Date ID]) as [%Date ID]
,1 as [Year end flag]
Resident D_Calendar_TMP2
Group by [Year end date];
join(D_Calendar_TMP2)
Load min([%Date ID]) as [%Date ID]
,1 as [Week start flag]
Resident D_Calendar_TMP2
Group by [Week start date];
join(D_Calendar_TMP2)
Load min([%Date ID]) as [%Date ID]
,1 as [Month start flag]
Resident D_Calendar_TMP2
Group by [Month start date];
join(D_Calendar_TMP2)
Load min([%Date ID]) as [%Date ID]
,1 as [Quarter start flag]
Resident D_Calendar_TMP2
Group by [Quarter start date];
join(D_Calendar_TMP2)
Load min([%Date ID]) as [%Date ID]
,1 as [Year start flag]
Resident D_Calendar_TMP2
Group by [Year start date];
NoConcatenate
D_Calendar:
Load [%Date SEQ]
,[%Date ID]
,[Calendar date]
,[Day of date]
,[Week of date]
,[Month of date]
,[Quarter of date]
,[Year of date]
,[Year week date]
,[Year month date]
,[week date]
,[quarter date]
,[Year quarter date]
,[Week start date]
,[Week end date]
,[Month start date]
,[Month end date]
,[Quarter start date]
,[Quarter end date]
,[Year start date]
,[Year end date]
,[%Year week SEQ]
,[%Year month SEQ]
,[%Year quarter SEQ]
,[Month Fact]
,Num ( (date([Calendar date],'DD/MM/YYYY') - date([Month start date],'DD/MM/YYYY')) + 1) AS [Month Diff]
,Num ( (date([Calendar date],'DD/MM/YYYY') - date([Quarter start date],'DD/MM/YYYY')) + 1) AS [Quarter Diff]
,Num ( (date([Calendar date],'DD/MM/YYYY') - date([Year start date],'DD/MM/YYYY')) + 1) AS [Year Diff]
,if(WeekEnd([Calendar date])=WeekEnd(Date(Today()-1,'DD/MM/YYYY')),Date(Today()-1,'DD/MM/YYYY'), WeekEnd([Calendar date])) as [Week end date 2]
,if(MonthEnd([Calendar date])=MonthEnd(Date(Today()-1,'DD/MM/YYYY')),date(Date(Today()-1,'DD/MM/YYYY'),'MMM YYYY'), date(MonthEnd([Calendar date]),'MMM YYYY') ) as [Month end date 2]
,if(QuarterEnd([Calendar date])=QuarterEnd(Date(Today()-1,'DD/MM/YYYY')),date(Date(Today()-1,'DD/MM/YYYY'),'MMM YYYY'), date(QuarterEnd([Calendar date]),'MMM YYYY') ) as [Quarter end date 2]
,if(YearEnd([Calendar date])=YearEnd(Date(Today()-1,'DD/MM/YYYY')),date(Date(Today()-1,'DD/MM/YYYY'),'MMM YYYY'), date(YearEnd([Calendar date]),'MMM YYYY') ) as [Year end date 2]
//,if([Calendar date]=Date(Today()-1,'DD/MM/YYYY'),1,[Week end flag]) as [Week end flag]
, if(date(floor( WeekEnd([Calendar date],0,-4)))=date(floor(WeekEnd(Today()-1,0,-4))),
if([Calendar date]=Date( '$(vCheckRevenuDate)','DD/MM/YYYY'),1),[Week end flag]) as [Week end flag]
,if(date(floor( MonthEnd([Calendar date])))=date(floor(MonthEnd(Date(Today()-1,'DD/MM/YYYY')))),
if([Calendar date]=Date( '$(vCheckRevenuDate)','DD/MM/YYYY'),1),[Month end flag]) as [Month end flag]
,if( date(floor(QuarterEnd([Calendar date])))=date(floor(QuarterEnd(Date(Today()-1,'DD/MM/YYYY')))),
if( [Calendar date]=Date('$(vCheckRevenuDate)','DD/MM/YYYY'),1) ,[Quarter end flag] ) as [Quarter end flag]
//,if([Calendar date]=Date(Today()-1,'DD/MM/YYYY'),1,[Year end flag]) as [Year end flag]
, if(date(floor( YearEnd([Calendar date])))=date(floor(YearEnd(Date(Today()-1,'DD/MM/YYYY')))),
if([Calendar date]=Date( '$(vCheckRevenuDate)','DD/MM/YYYY'),1),[Year end flag]) as [Year end flag]
, [Week start flag]
, [Month start flag]
, [Quarter start flag]
, [Year start flag]
// ,if(date(floor(MonthEnd([Calendar date])))=date(floor(MonthEnd(Date(Today()-1,'DD/MM/YYYY')))),
// if([Calendar date]=Date('$(vCheckRevenuDate)','DD/MM/YYYY'),1),[Month start flag] ) as [Month start flag]
,AutoNumber(Num(Month([Month Fact]), '00')&'/'&Year([Month Fact]), '%Year month fact SEQ') as [%Year month fact SEQ]
Resident D_Calendar_TMP2;
Drop table D_Calendar_TMP, D_Calendar_TMP2;
DONT't change a thing
Then, in your fact table, in which you have the date field; do as follow:
AutoNumber(date(YourDateField, 'DD/MM/YYYY'),'%Date ID') as [%Date ID]
Now: as an expression for ur rolling month:
use this:
sum({<[Calendar date]=,[Month of date]=, [Year of date]=, [%Year month SEQ] = {"> $(= max([%Year month SEQ]) - 12) <= $(=[%Year month SEQ])"}>}QUANTITY)
and a dimension use :
date([Month end date 2], 'MMM-YYYY')
Now, when you choose a Mars 2017 for example, you'll have:
Hi Wallo,
The first expression worked. It is the way I format the dates.
The second SA expression for rolling 13 does not work.
Do I need to implement a master calendar ?