Max month in month year

[Anonymous]

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.

Example :

1. 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

1. 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.

Dimension : month_year

Exp: Count({ <date_of_join_Month_Year={"$(=date(max(date_of_join_new),'MMM-YY'))"} >}Empcode)

this gives me correct numbers in bar chart but only for latest month it dosent display chart for previous months.

[Anonymous]

Thank you so much it works fine, but the only issue left is if I read my data from qvd files it dosent compare the data fields like in expression MonthYr_Month = DOJ_Month even if both values (MonthYr_Month and DOJ_Month )  are Aug2018 it dosent count the sum for it.

[Anonymous]

I means if my both columns Month_Year and DOJ are in text format I am running into the problem it dosent comapre like in case of attached excel. If I use the logic shared by you it wont work with the attached file, it looks like format issue which i could not figure out.

[vishsaggi · Accepted solution]

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:

Empcall:

LOAD Month_Year,

     EMPID,

     DOJ

FROM

(biff, embedded labels, table is Sheet1$);

NoConcatenate

Final:

LOAD *,

     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

Resident Empcall;

Drop Table Empcall;

LEFT JOIN(Final)

LOAD MonthYr_Month,

     Sum(IF(MonthYr_Month = DOJ_Month, 1, 0)) AS DOJCnt    

Resident Final

Group By MonthYr_Month;

[Anonymous]

Ya .Thank yo so much it works fine.

So is it like when its text its we should use floor funct, else num# function

[vishsaggi]

‌No problem. Well I did not try anyother way. So we have to change the formatting based on what we get from excel.

[Anonymous]

Thanks Vishwarath. One point of concern.

If I want to show this DOJCnt at different cuts like zone, region then it would be troublesome as I have grouped at MonthYr_Month level at backend script.

Like earlier you suggested can we achieve same thing now using below exp at front end if we want to show dimension at different cuts like zone, region

Add bar chart:

Dim: zone/region/month_year

Expr:

= Count(IF(MonthYr_Month = DOJ_Month,  EMPID))

[vishsaggi]

Can you share some sample data and what you are expecting to see the count for those dimensions please...will look into it.

[Anonymous]

Thank you Vishwarath.

I have attached the sample data set only for Aug dataset.

Logic:

1. Existing logic shared by you i.e MonthYr_Month= DOJ_Month is the same here.

2. Since we have done the same at backend at using group by MonthYr_Month, issue arrises if I want to view at Zone level or any other level. If at back end I would do group by MonthYr_Month, Zone then other filters wont be applicable eg if at chart I want to filter at Code level or any other filter (attached excel).

3. Attached is the sample output.

4. If for North Region I filter for Code "2" from front end the chart should display count(ID) as 2

5. If for North Region I filter for Code "1" from front end  the chart should display count(ID) as 1

Thanks.