I have multiple files for each month with same structure. I have concatenated these files and calculated Month Year from each months file like Jan-18, Feb-18 etc. In these files there are field ID, Opening Date . In field "Opening date" which could include any date like in Jan month file Opening Date value could be 01/12/1978, 10/09/2017 or 07/08/2018.
My requirement is to plot one bar chart with dimension as Month year (Jan-18, Feb-18, Mar-18...) and in expression i want to take count of ID only for the maximum month in that field (Opening Date) within that file.
There is single bar chart with month year.
1. Jan month file
Dimension value : Jan-18
And if suppose Opening date values are like 01/03/1998, 01/05/2017,01/06/2018
Then here max month year is 01/06/2018
In measure i would count(all ID for the max month year) in jan month file
2. Feb month file
Dimension value: Feb-18
And if suppose Opening date values are like 01/03/1997, 10/07/2017,11/08/2017
Then here max month year is 11/08/2017
In measure i would count(all ID for the max month year) in Feb month file.
Formatting is pretty tricky when pulling data from excel so based on your excel column values we need to use formatting functions at qlikivew side to convert them to consistent dates based on our requirements. So just changed the formatting a little bit try below:
(biff, embedded labels, table is Sheet1$);
Text(Date(Floor(Date#(Month_Year, 'DD/MM/YYYY')),'MMYYYY')) AS MonthYr_Month,
Text(Date(Floor(Date#(DOJ, 'DD/MM/YYYY')),'MMYYYY')) AS DOJ_Month
Drop Table Empcall;
Sum(IF(MonthYr_Month = DOJ_Month, 1, 0)) AS DOJCnt