Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join this live chat April 6, 10AM EST - QlikView to Qlik Sense REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
NITROG3NN
Partner
Partner

Max date per month for 6 months dynamic in Bar chart

Hi All,

My requirement is to get Data of Max Date per month in Bar chart but for 6 months and it should be Dynamic.

Following is my Expression which I am using and getting data of max date for 6 Months.

num(Sum({<Date={">=$(=Date(Num(MonthStart(AddMonths(Max(Date),-6))),'YYYY/MM/DD'))<=$(=Date(Num(Max(Date),-1),'YYYY/MM/DD'))"},MonthYear=>}
Aggr(If(Date = Max(TOTAL <MonthYear> Date),
count({<Date={">=$(=Date(Num(MonthStart(AddMonths(Max(Date),-6))),'YYYY/MM/DD'))<=$(=Date(Num(Max(Date),-1),'YYYY/MM/DD'))"},MonthYear=,
[Difference Category]={'7Days'}>}Distinct [DEVICE NO])),Date, MonthYear))

/

Sum({<Date={">=$(=Date(Num(MonthStart(AddMonths(Max(Date),-6))),'YYYY/MM/DD'))<=$(=Date(Num(Max(Date),-1),'YYYY/MM/DD'))"},MonthYear=>}
Aggr(If(Date = Max(TOTAL <MonthYear> Date),
count({<Date={">=$(=Date(Num(MonthStart(AddMonths(Max(Date),-6))),'YYYY/MM/DD'))<=$(=Date(Num(Max(Date),-1),'YYYY/MM/DD'))"},
MonthYear=>}Distinct [DEVICE NO])),Date, MonthYear)),'0.0%')

 

This works fine the only issue is when I select any Month the bar chart only shows data for that month, I am not able to get Dynamic 6 Months when I select any Month.

This logic is given by @sunny_talwar 

#https://community.qlik.com/t5/New-to-Qlik-Sense/Max-Date-per-Month/m-p/1527312#M123147

1 Reply
edwin
Specialist III
Specialist III

i find that if one of the selected filters is one of your aggr fields, it will always respect the filter.  even if you include an ignore for YearMonth, it will still respect the filter.  what i would do is create an island table with Year Month and let the user select that, then in your set analysis, add modifers that limit the actual year months.  i expanded on Sunny's solution to limit for a period of 6 months and return the date with max data for each month - see attached if it helps you.