Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Max month in month year

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.

1 Solution

Accepted Solutions
vishsaggi
Champion III
Champion III

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;

View solution in original post

17 Replies
Anonymous
Not applicable
Author

test.PNG

vishsaggi
Champion III
Champion III

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
Not applicable
Author

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
Champion III
Champion III

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

Anonymous
Not applicable
Author

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
Champion III
Champion III

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))

Capture.PNG

Anonymous
Not applicable
Author

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-MonthYear1.PNG

vishsaggi
Champion III
Champion III

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
Champion III
Champion III

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

Capture.PNG

IF yes, see attached.