flexible Date dimension according to location close date

[johnmackintosh]

Hi

I'm plotting counts of events happening over time, by month, using MonthYear as my chart dimension.

What I want is that the dimension is always populated, even during months where no events occur.

However, if the selected location has a close date, then the dimension should not display any points after that month.

To put it another way  if there is no close date, display all points up to current month, with a zero displayed for any months were no events happened.

If there is a close date, display a zero for months where no events happened, but do not display any points after the month of the close date.

my  main chart expression was originally

Sum ([Count]) + Sum({1} 0) to ensure in months where no event occurs, the chart displays a zero.

Some locations have closed, and there is  a close date field.

I have wrapped that with the MonthName function, creating a PlotEndDate, which is either the Month Year of the close date, or the current maximum Month Year.

I tried using

=IF(MonthYear<=[PlotEndDate],Sum ([Count]) + Sum({$} 0),Sum ([Count]) )as my main expression.

My calculate  dimension is IF(MonthYear<=[PlotEndDate],MonthYear)

That works, in that if there is a closed date, I don't get any zero values plotted for months beyond that.

However, it doesn't satisfy the need to plot a zero for any months prior to the close date, where no event occurred.

If I change the chart expression

=IF(MonthYear<=[PlotEndDate],Sum ([Count]) + Sum({1} 0),Sum ([Count]) ),  then I'm back to have all months plotted, even past the close date.

Any thoughts or pointers on the best way to achieve this? Or is it not possible because its almost looking for conditional nulls?

EDIT -  with regards to the dimension, I have experimented with suppress when value is null, and generally I get the best results with this selected

Message was edited by: John MacKintosh Added extra info about suppress when value is null

[sunny_talwar]

Or this... if you would want to exclude Oct 2016

=IF(MonthYear < Only(TOTAL [PlotEndDate]), MonthYear)

[johnmackintosh]

This is really close Sunny, thanks !

Clearly I need to do some more reading into the ONLY function

When I select an individual area, if it has a close date, no points are plotted beyond that , and zeroes are displayed as appropriate, which is what I want.

When I clear all selections, then I have a blank chart ( because the dimension value becomes NULL ) instead of a chart that aggregates all the events across all months.

I realise the example data I gave you only had 1 location so you met the requirements based on that data.

I'm trying to figure out if this is possible with an if statement on both dimension and expression, but I'm failing hard

[sunny_talwar · Accepted solution]

How about this

=Aggr(If(MonthYear < Only(TOTAL <Location> [PlotEndDate]), MonthYear), Location, MonthYear)

[johnmackintosh]

I really appreciate your help here Sunny.

So THAT works, in that I now get the total across all locations ( I used Location ID instead of < Location>  after the TOTAL qualifier and also in the Aggr function) because there are hierarchies of locations as well).

It isn't plotting the zero between values between actuals when I select  certain locations which have a close date.

Yet it works for other locations.

I don't think its fair to expect you to come up with any more answers, I can't share the true underlying data.

I'll mark your answer above as correct for now, I don't see how else to do this.

Genuinely, thanks for your help. Seems that ONLY and AGGR are nearly always involved in the hard stuff!!

[sunny_talwar]

Like you mentioned... it is difficult to know without being able to see what you have. Few ideas

1) If you want to share anonymised version of QlikView dashboard... you can look here

Preparing examples for Upload - Reduction and Data Scrambling 

2) It seems like you might have an issue related to missing data... may be this can help

Generating Missing Data In QlikView

[johnmackintosh]

Thanks again. I'll take a look at that generating missing data document. I'm relying on chart expressions rather than pre-aggregating in the script as there are simply too many combinations of  selections and locations.

Thanks for all your help

John