Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

drillnaut
Valued Contributor

Bar Chart: Last 6 months from Max([Stop Arrival Date])

Hello Folks,

I think I've outsmarted myself and I can't think my way out of this problem.  I have delivery stops, that have a field called [Stop Arrival Date].  What I would like, is that I have a Bar Chart that looks at the Max([Stop Arrival Date]) of the possible [Stop Arrival Date] (taking into account user selections) and then it shows 3 months back worth of data.

Right now, the issue is that I have to Measure to take into account the selection of the Max([Stop Arrival Date]) but ignore the selection when bringing back data i.e when I click March it only brings back data for March, and not February and January.  Currently, I have the following.

Dimension:

=If( [Stop Arrival DateTime] >

     date(interval(Max(Total [Stop Arrival DateTime]  - 120,'D'),'YYYY/MM/DD') ),

     StopMonth )

Measure:

SUM({1<IsEmpty={1},[Stop Arrival Date]= {'>=$(=date(Max(TOTAL [Stop Arrival Date]) - 120))'}>} stp_lgh_mileage)

The output?  This mess right here with every month under the sun. Selection={Year = 2017, StopMonth = Mar}.

d5a9ff4962664f4f902e24ac36d4b58f.png

Any help is greatly appreciated.

1 Solution

Accepted Solutions
MVP
MVP

Re: Bar Chart: Last 6 months from Max([Stop Arrival Date])

May be this

=If([Stop Arrival DateTime] > (Max(Total [Stop Arrival DateTime])  - 120), StopMonth)

5 Replies
MVP
MVP

Re: Bar Chart: Last 6 months from Max([Stop Arrival Date])

May be this

=If([Stop Arrival DateTime] > (Max(Total [Stop Arrival DateTime])  - 120), StopMonth)

MVP
MVP

Re: Bar Chart: Last 6 months from Max([Stop Arrival Date])

And this measure

SUM({1<IsEmpty={1}>} stp_lgh_mileage)

MVP
MVP

Re: Bar Chart: Last 6 months from Max([Stop Arrival Date])

And this measure

SUM({1<IsEmpty={1}>} stp_lgh_mileage)

drillnaut
Valued Contributor

Re: Bar Chart: Last 6 months from Max([Stop Arrival Date])

I'm a blistering idiot.  The [Stop Arrival DateTime] and the [Stop Month] field reside on different tables.  This leads to their connecting key being very important, but also very hidden since I thought they were on the same table.  This was my final solution.

Dimension:

=If([StopCalendarDate] > Date(Max(Total [StopCalendarDate])  - 120)

AND [StopCalendarDate] <= Date(Max(Total [StopCalendarDate])), StopMonth)

Measure:

SUM({1<IsEmpty={1}>} stp_lgh_mileage)

MVP
MVP

Re: Bar Chart: Last 6 months from Max([Stop Arrival Date])

You don't really need formatting function such as Date() in your if condition. If this makes viewing easy, then you can continue using it... but this should do also

=If([StopCalendarDate] > (Max(Total [StopCalendarDate])  - 120)

AND [StopCalendarDate] <= (Max(Total [StopCalendarDate])), StopMonth)