Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Friends,
if i select january month , data should fetch from 2nd jan 2016 to 1 st feb 2016
name | date | sal |
a | 11-Jan-16 | 10 |
b | 20-Jan-16 | 20 |
c | 1-Feb-17 | 30 |
a | 11-Feb-16 | 40 |
b | 20-Feb-16 | 50 |
c | 1-Mar-17 | 60 |
as per above data
if i select Jan month, sum (sal) displaying (10+20+30)=60 but chart level displaying month wise for jan (10+20)=30 and for feb 30 ,my requirement is it should display for jan month 60
same if i select feb ,sum(sal)=150(40+50+60),but chart level displaying month wise for feb(40+50)=90 and for mar 60,in this case as per my requirement it should display for feb month 150.
@Team,any idea?
Hi,
This works for me as you explained above:
using a modification of code from rwunderlich 's cook book and Qlikview Masters Summit resources:
Sample attached...
Sal: //***** Tablename
LOAD name,
date#(date,'DD-MMM-YY') as SAL_DATE, //***** Fieldname
sal
FROM
[https://community.qlik.com/thread/248237]
(html, codepage is 1252, embedded labels, table is @1);
QuartersMap:
MAPPING LOAD
rowno() as Month,
'Q' & Ceil (rowno()/3) as Quarter
AUTOGENERATE (12);
Temp:
Load
min(SAL_DATE) as minDate, //***** Fieldname
max(SAL_DATE) as maxDate //***** Fieldname
Resident Sal; //***** Tablename
Let varMinDate = Num(Peek('minDate', 0, 'Temp'));
Let varMaxDate = Num(Peek('maxDate', 0, 'Temp'));
DROP Table Temp;
TempCalendar:
LOAD
$(varMinDate) + Iterno()-1 As Num,
Date($(varMinDate) + IterNo() - 1) as TempDate
AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate);
* Updated to handle when month is 1
MasterCalendar:
Load
TempDate AS SAL_DATE, //***** Fieldname
week(TempDate) As Week,
Year(TempDate) As Year,
if(TempDate = MonthStart(TempDate),
if(Month(TempDate) = 1, 12, Month(TempDate)-1),
Month(TempDate)) As Month,
Day(TempDate) As Day,
YeartoDate(TempDate)*-1 as CurYTDFlag,
YeartoDate(TempDate,-1)*-1 as LastYTDFlag,
inyear(TempDate, Monthstart($(varMaxDate)),-1) as RC12,
date(monthstart(TempDate), 'MMM-YYYY') as MonthYear,
ApplyMap('QuartersMap', month(TempDate), Null()) as Quarter,
Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as WeekYear,
WeekDay(TempDate) as WeekDay
Resident TempCalendar
Order By TempDate ASC;
Drop Table TempCalendar;
Anat
Your chart is behaving as it should as 1st Feb is in February. It seems you want to offset your calendar by a day so a month always begins on the 2nd is that correct? and the 1st of every month belongs to the month before?
Are you using a separate calendar table at the moment?