Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld, June 24-25, 2020. Free virtual event for DI and DA gurus. Register Today
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor II
Contributor II

Rolling 13 month chart and table...why so difficult?

So my customer comes to me and says:  Hey I want to have a report that shows a chart of rolling 13 month sales data for my team, and I want a table showing the same data underneath it.

Me: Should be no problem

...

Me: Here you go customer:

CustomerThat looks pretty good, but where is August 2016 and January 2017?

Me: Your team didn't have any sales for those months.

Customer: So?!  I still want to see the month!  It isn't exactly a rolling 13 month report if it only shows 11 months!

Me: Hmm, good point, let me see what I can do

...

Me: Here you go customer:

Customer: That's looking better, I see all the months...no wait...what is that extra month at the end?

Me: I don't know.

Customer: And who is that extra salesperson in yellow?  Sales person "-"???

Me: I don't know.

Customer: Wait there are actual figures for that extra month in the table?!  Where are those coming from??

Me: I don't know.

Customer: This is unacceptable.

Me: Let me see what I can do.

...

Me: Here you go customer.

Customer: Oh good the months look right now.  Wait...that mystery Sales Person is still there.

Me: I know

Customer: What gives?  I can't show my managers a report with that in there.

Me: QlikView appears to be unable to handle this particular situation adequately.

Customer: You know I could do this in about 30 minutes in Excel exactly the way I want it.

Me: Yes, I know, I could too.

Customer: The only reason I bought these expensive servers, software, and your time is so that I could have this automated.

Me: Yes, I understand.

Customer: I'm NOT happy.

---------------------------------------------------

So this was, of course, a fictional story, but situations like this seem to happen again and again.

I've attached the sample data as well as three QVWs that represent the three examples above.  QlikView is an amazing tool and can do amazing things, but it is very difficult to explain to customers when something seemingly as simple as this causes issues.

For this particular Rolling 13 month scenario I have tried all of the following:

  1. An expression for the Date Dimension that limits it to the past 13 months
  2. Every combination of "Suppress When Value Is Null" and "Show All Values" that I can think of on both Dimensions
  3. Every combination of "Suppres Zero-Values", "Suppress Missing" and "Populate Missing Cells" I can think of
  4. ValueList based dynamic Dimensions for Date
  5. Simple if..then expressions for the sales amount
  6. Set Analysis based expressions for the sales amount
  7. Individual expressions for each month (works on the table, not on the chart)

The only thing I haven't tried, and seems like a real hack, would be to populate the data with a dummy record for each SalesPerson for each month with zero value so there is at least one entry each month for each person.  I may try it just to see if it works, but in the real situation, where there are millions of rows in the source data, this would be very unpalatable.

I hope I am missing something obvious here, and I would be ecstatic to be told as much.

4 Replies
Highlighted

Re: Rolling 13 month chart and table...why so difficult?

Can you try this? I am assuming this. I haven't check the file

=Sum({$<Mon-Year={'>=$(=MonthName(AddMonths(Max(Mon-Year),-13))) <=$(=MonthName(Max(Mon-Year)))'}>} Sales)


PS - Please UN-check the suppress from presentation tab and then same in Dimension for Mon-Year dimension

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
Highlighted
MVP
MVP

Re: Rolling 13 month chart and table...why so difficult?

I liked your fictional story. Have a look here: How to populate a sparsely populated field

Highlighted

Re: Rolling 13 month chart and table...why so difficult?

Hi,

You already noticed the below points.

1. Sales person names are null in most of the months (but amount is there)

2. if we do suppress null, the month doesn't have amount will go off. (e.g Aug-2016)

Capture1.JPG

3. Try to change the MonYear field as Proper field. It seems to be a text.

Like below

Capture.JPG

Dimension:_


MonYear

=Aggr(if(sum(TOTAL <MonYear> Amount)<>0,SalesPerson,''),MonYear,SalesPerson)

Expression

Sum({$<MonYear={'>=$(=AddMonths(Max(MonYear),-13)) <=$(=Max(MonYear))'}>} Amount)

Find the Attachment

Capture2.JPG

Highlighted
Contributor II
Contributor II

Re: Rolling 13 month chart and table...why so difficult?

Settu,

Thanks for the reply.  I follow what you are doing here I think.  In the Dimension you are elminating any SalesPerson who has never had any sales and thus eliminating the mystery "-" person.

With the expression you are limiting the data to the last 13 months.

Does that sound right?

I don't quite follow the change to the fields in the Master Calendar.  Is that just for easier sorting purposes?

I did notice one thing, and I know this isn't part of my original story, but when I select additional criteria from the List Boxes, such as "Product Category" it goes back to skipping months.

Many thanks!