Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
FileID | Propose | UnderDev |
1 | 3/11/2016 | 3/28/2016 |
2 | 3/25/2016 | 4/1/2016 |
3 | 4/1/2016 | 4/15/2016 |
I need to create line chart with 2 line which show count of each dates by Month.
In march Propose count is 2 and In Apr 1
In march Underdev count is 1 and In Apr 2
I may have to combine these 2 dates ? I am not sure.Can you help me ?Thank you.
Actually what Stefan mentioned works better I guess
Dimensions:
Month
Flag
Expression: Count(Flag)
You could create a canonical date out of these two date fields:
Then link the canonical date to a master calendar, and use the master cal. month as dimension.
Second dimension could be your date type field and expression =Count(Type)
sunindia Do yoy have any solution for this please ?
Thank you and tried this approach but didnt work. I am still a beginner here !
Hi,
As suggested by swehul, you can try Canonical Date with link of your master calendar.
still, If you want the solution based on your provided data, may be the below script will helps (this is not the good way)
T1:
LOAD * INLINE [
FileID, Propose, UnderDev
1, 3/11/2016, 3/28/2016
2, 3/25/2016, 4/1/2016
3, 4/1/2016, 4/15/2016
];
T2:
CrossTable(Type,Date)
LOAD * Resident T1;
New:
LOAD *,Month(Date) as Month Resident T2;
DROP Table T1,T2;
Canonical Calendar method:
Table:
LOAD * Inline [
FileID, Propose, UnderDev
1, 3/11/2016, 3/28/2016
2, 3/25/2016, 4/1/2016
3, 4/1/2016, 4/15/2016
];
LinkTable:
LOAD FileID,
Propose as Date,
'Propose' as Flag
Resident Table;
Concatenate(LinkTable)
LOAD FileID,
UnderDev as Date,
'UnderDev' as Flag
Resident Table;
Temp:
LOAD Max(Date) as MaxDate,
Min(Date) as MinDate
Resident LinkTable;
LET varMaxDate = Peek('MaxDate');
LET varMinDate = Peek('MinDate');
DROP Table Temp;
Calendar:
LOAD Date,
Month(Date) as Month,
Year(Date) as Year,
MonthName(Date) as MonthYear;
LOAD Date($(varMinDate) + IterNo() - 1) as Date
AutoGenerate 1
While $(varMinDate) + IterNo() -1 <= $(varMaxDate);
Dimension: Month
Expressions:
1) Count({<Flag = {'Propose'}>}Flag)
2) Count({<Flag = {'UnderDev'}>}Flag)
Actually what Stefan mentioned works better I guess
Dimensions:
Month
Flag
Expression: Count(Flag)
Thank you as always You are my role model