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]

[vishsaggi]

May be try like:

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

OR

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

IF you want to go 12months back try like

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

[Anonymous]

Thanks for answering Vishwarath.

Using the expression provided it shows the total count of ID  in all the respective month.

But I want in for each month count(all id for max month value in that file)

[vishsaggi]

Can you share some sample file to look into and your expected values you want to see?

[Anonymous]

Suppose attached are 3 excel files for each month (Jan,Feb and March)

Dimension would display : Jan-18, Feb-18,Mar-18

In Jan month file there are 2 ID's where DOJ is max (Jan month)

In Feb month file there are again 2 ID's where DOJ is max(Feb month)

In March month file there is 1 ID's where DOJ is max(March month)

So in bar chart for dimension Jan-18 value will be 2 (measure value), Feb-18 value will be 2 and for Mar-18 value will be 1

[vishsaggi]

May be try this?

EmployeeJoining:

LOAD Month_Year,

     EMPID,

     DOJ

FROM

[..\CRCQlik\201801.xlsx]

(ooxml, embedded labels, table is Sheet1);

LOAD Month_Year,

     EMPID,

     DOJ

FROM

[..\CRCQlik\201802.xlsx]

(ooxml, embedded labels, table is Sheet1);

LOAD Month_Year,

     EMPID,

     DOJ

FROM

[..\CRCQlik\201803.xlsx]

(ooxml, embedded labels, table is Sheet1);

NoConcatenate

Final:

LOAD *,

     Month(Num#(Date#(DOJ, 'D/M/YYYY'))) AS DOJ_Month,

     Month(Num#(Date#(Month_Year, 'D/M/YYYY'))) AS MonthYr_Month

Resident EmployeeJoining;

Drop Table EmployeeJoining;

Add bar chart:

Dim: Month_Year

Expr:

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

[Anonymous]

Thanks for the answer.

If in file 201801 DOJ is jan -17, Jan-18, Jan-16 .. then measure value should be 1 as max is (Jan-18) but currently it will give us count as 3.

Have attached file; it should be for max-MonthYear

[vishsaggi]

What is the expected output you are looking for for your above data?  I mean what are the counts you are want to see for Jan, Feb and Mar. Are we counting EMPID or the month field?

[vishsaggi]

Based on your attached files this is what i am getting? Are you looking for the same?

IF yes, see attached.