Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I have a bar chart with below
Dimension1: Month
Dimension2: =IF(FiscalWeekSerial>=$(curFiscalWeekSeq)-4,FiscalWeek)
Expression as below:
% of Fail: Count({<[Overall Result]={'Fail'}>} Distinct [ID])/Count(Distinct([ID]))
Number Review:Count(Distinct([ID]))
Number of Fail: Count({<[Overall Result]={'Fail'}>}DISTINCT [ID])
In bar chart the Y axis values are showing as 0%,100%,200%,300%,400% and so on up to 1,200%
There are Number of Fail as 2. Because of this the Number of Fail is not showing? Is there any way that I can change the Y axis values?
Thanks.
Thanks. I tried changing Date field still showing the same result. I can able to see the count of Fail in straight table. But the problem is with Bar chart?
For Fail condition there is no dates in the data model thats why values not plotted check the base table for this why date is not coming because you have date dimension.
Hope this helps
Can you upload the updated file i.e. Update Date
Thanks. Just now I found the error. Actually I need to take the data from four sheets. And I need to create a MasterCalendar based on these four sheets dates and the dates are as below .
Please help me how to combine all dates into one MasterCalendar?
Sheet A Dates
14-Jan-14
14-Jan-14
14-Jan-14
14-Jan-14
16-Jan-14
16-Jan-14
16-Jan-14
16-Jan-14
16-Jan-14
16-Jan-14
16-Jan-14
16-Jan-14
16-Jan-14
16-Jan-14
04-Feb-14
04-Feb-14
12-Feb-14
12-Feb-14
12-Feb-14
12-Feb-14
12-Feb-14
19-Mar-14
19-Mar-14
19-Mar-14
19-Mar-14
19-Mar-14
19-Mar-14
19-Mar-14
19-Mar-14
19-Mar-14
19-Mar-14
19-Mar-14
19-Mar-14
19-Mar-14
19-Mar-14
21/01/2014
21/01/2014
21/01/2014
21/01/2014
21/01/2014
22/01/2014
22/01/2014
29/01/2014
29/01/2014
29/01/2014
29/01/2014
03/02/2014
03/02/2014
05/02/2014
05/02/2014
05/02/2014
05/02/2014
05/02/2014
18/02/2014
18/02/2014
18/02/2014
19/02/2014
19/02/2014
19/02/2014
24-Mar-14
24-Mar-14
24-Mar-14
24-Mar-14
24-Mar-14
Sheet B Dates
24-Feb-14
24-Feb-14
24-Feb-14
28-Feb-14
28-Feb-14
20-Jan-14
20-Jan-14
21-Jan-14
22-Jan-14
28-Jan-14
29-Jan-14
10-Mar-14
22/01/2014
22/01/2014
22/01/2014
05/02/2014
13/02/2014
13/02/2014
20-Feb-14
20-Feb-14
10-Mar-14
10-Mar-14
05/02/2014
05/02/2014
19/02/2014
19/02/2014
18/03/2014
04/02/2014
04/02/2014
04/02/2014
04/02/2014
04/02/2014
04/02/2014
05/02/2014
05/02/2014
12/02/2014
12/02/2014
13/02/2014
13/02/2014
13/02/2014
18/02/2014
18/02/2014
20-Feb-14
19/03/2014
28-Jan-14
24-Mar-14
Sheet C Dates
08/01/2014
08/01/2014
21/01/2014
21/01/2014
03/02/2014
03/02/2014
05/02/2014
05/02/2014
29/01/2014
29/01/2014
05/02/2014
05/02/2014
05/02/2014
05/02/2014
05/02/2014
20/01/2014
20/01/2014
20/01/2014
30/01/2014
30/01/2014
07/01/2014
07/01/2014
14/01/2014
14/01/2014
22/01/2014
22/01/2014
22/01/2014
03/02/2014
03/03/2014
03/02/2014
Sheet D Dates
07-Jan-14
07-Jan-14
07-Jan-14
08-Jan-14
13-Jan-14
13-Jan-14
14-Jan-14
14-Jan-14
17-Jan-14
17-Jan-14
20-Jan-14
20-Jan-14
21-Jan-14
22-Jan-14
22-Jan-14
27-Jan-14
29-Jan-14
29-Jan-14
30-Jan-14
30-Jan-14
31-Jan-14
03-Mar-14
03-Mar-14
03-Feb-14
04-Feb-14
12-Feb-14
12-Feb-14
17-Feb-14
21-Feb-14
25-Feb-14
25-Feb-14
05-Mar-14
05-Mar-14
06-Mar-14
06-Mar-14
10-Mar-14
10-Mar-14
10-Mar-14
10-Mar-14
20-Jan-14
20-Jan-14
29-Jan-14
30-Jan-14
12-Feb-14
12-Feb-14
05-Mar-14
14-Mar-14
14-Mar-14
19-Mar-14
30-Jan-14
03-Feb-14
04-Feb-14
12-Feb-14
19-Feb-14
02/01/2014
17/01/2014
17/01/2014
21/01/2014
21/01/2014
22/01/2014
28/01/2014
29/01/2014
29/01/2014
29/01/2014
30/01/2014
30/01/2014
31/01/2014
03/02/2014
04/02/2014
04/02/2014
12/02/2014
12/02/2014
18/02/2014
19-Feb-14
25-Feb-14
28-Feb-14
28-Feb-14
05-Mar-14
07/01/2014
07/01/2014
09/01/2014
09/01/2014
09/01/2014
14/01/2014
14/01/2014
14/01/2014
17/01/2014
17/01/2014
17/01/2014
20/01/2014
20/01/2014
20/01/2014
20/01/2014
21/01/2014
21/01/2014
27/01/2014
04/02/2014
21/02/2014
18/03/2014
08/01/2014
08/01/2014
08/01/2014
14/01/2014
14/01/2014
14/01/2014
20/01/2014
20/01/2014
20/01/2014
20/01/2014
20/01/2014
20/01/2014
23/01/2014
23/01/2014
23/01/2014
27/01/2014
27/01/2014
30/01/2014
30/01/2014
30/01/2014
31/01/2014
03/03/2014
03/03/2014
05/02/2014
12/02/2014
12/02/2014
12/02/2014
12/02/2014
12/02/2014
12/02/2014
17/02/2014
18/02/2014
18/02/2014
18/02/2014
18/02/2014
18/02/2014
02-Jan-14
02-Jan-14
07-Jan-14
08-Jan-14
08-Jan-14
09-Jan-14
13-Jan-14
14-Jan-14
16-Jan-14
21-Jan-14
22-Jan-14
23-Jan-14
23-Jan-14
23-Jan-14
28-Jan-14
28-Jan-14
28-Jan-14
28-Jan-14
28-Jan-14
31-Jan-14
12-Feb-14
13-Feb-14
17-Feb-14
17-Feb-14
18-Feb-14
18-Feb-14
21-Feb-14
21-Feb-14
21-Feb-14
21-Feb-14
21-Feb-14
28-Feb-14
28-Feb-14
28-Feb-14
05-Mar-14
05-Mar-14
10-Mar-14
10-Mar-14
10-Mar-14
10-Mar-14
10-Mar-14
14-Mar-14
10-Mar-14
10-Mar-14
10-Mar-14
10-Mar-14
14-Mar-14
14-Mar-14
14-Mar-14
14-Mar-14
14-Mar-14
14-Mar-14
14-Mar-14
14-Mar-14
17-Mar-14
17-Mar-14
17-Mar-14
17-Mar-14
17-Mar-14
17-Mar-14
17-Mar-14
MasterCalendar is
TRACE Creating Master Calendar;
MinMax:
LOAD
Max(TIMESTAMP([Date])) AS MaxDate,
Min(TIMESTAMP([Date])) AS MinDate
FROM
DD.xlsx
(ooxml, embedded labels, table is Sheet A);
/* STORE earliest and latest dates in variables */
LET varMinDate = Num(Peek('MinDate', -1, 'MinMax'));
LET varMaxDate = Num(Peek('MaxDate', -1, 'MinMax'));
/* STORE todays date in variable */
LET varToday = Num(Today());
QuarterMap:
MAPPING LOAD * INLINE [
Month, Quarter
1, Q1
2, Q1
3, Q1
4, Q2
5, Q2
6, Q2
7, Q3
8, Q3
9, Q3
10, Q4
11, Q4
12, Q4
];
/* Autogenerate every date between varMinDate and varMaxDate while creating calendar fields */
MasterCalendar:
LOAD*,
FiscalYear & '/ W' & FiscalWeek AS FiscalYearWeek;
LOAD*,
YearStart(Date,0,7) AS FiscalYearStart,
YearName(Date,0,7) AS FiscalYear,
AUTONUMBER(Year(Date) & Week(Date)) AS FiscalWeekSerial,
ROUND(num(([Date]+184-(MakeDate(Year(([Date]+184)-mod(([Date]-2),7)+3), 1, (mod(([Date]-2),7)+1) )-10))/7-0.5,'#,##0')) AS FiscalWeek,
'Q' & CEIL(if(num(Month(Date))<7,num(Month(Date))+6,num(Month(Date))-6)/3) AS FiscalQuarter;
LOAD
Timestamp(Date) as Date_TS,
Date#(Date(Date)) AS Date,
IF(Date = Today(),1) AS Today,
week(Date) AS Week,
Year(Date) AS Year,
Month(Date) AS Month,
Day(Date) AS Day,
WeekDay(Date) AS WeekDay,
ApplyMap('QuarterMap', Num(Month(Date)), Null()) AS Quarter,
Date(MonthStart(Date),'MMM-YYYY') AS MonthYear,
Week(Date) & '-' & Year(Date) AS WeekYear,
InYearToDate(Date, $(varToday), 0) * -1 AS CurYTDFlag,
InYearToDate(Date, $(varToday), -1) * -1 AS LastYTDFlag;
LOAD
Date($(varMinDate) + RecNo() - 1) AS Date
AUTOGENERATE ($(varMaxDate) - $(varMinDate)+1);
/* DROP Temporary TABLE */
DROP TABLE MinMax;
STORE MasterCalendar INTO C:\MasterCalendar.qvd;
DROP TABLE MasterCalendar;
First you need to make all your different date formats to single date format...
I have done the same in enclosed file.
Now rest you can do yourself.
Perfect. Thanks.