Dynamically changing chart based on "snapshot" data question...

[#1]

Hi,

I have been tasked to create an application in QV based on a report that currently exists in excel.

The excel has a line graph that is based on the chart below.  The data under the column 'Dec' is data from the current report/month.  The data under the other two columns, 'Nov' and 'Oct' are from previous months.  When the person runs this report for a new month, he simply moves December and November over to the right keeping the data intact, eliminates October, and creates a new column on the left for the new month(January).  January will have data from the current report now, whereas the data under columns Dec and Oct are from previous runtimes.

The row labels are a running 13 month.  So in February we will see February 2013 all the way down to February 2014.  In March we will see March 2013 all the way down to March 2014, all while the three columns shifting accordingly as detailed in the previous paragraph.  For now, ignore the W-W Trend column.

How can I simulate this in Qlikview?

I think/thought I am on the right track creating an Inline table and populating it with the data shown below.  I created the graph and it looked perfect.  But then next month, how can I simulate this dynamically/automatically for the new data ?

Table for Chart
W-W Trend		Dec	Nov	Oct
Even	Jan	0	0	0
Even	Feb	1	1	1
Even	Mar	0	0	0
Even	Apr	1	1	1
Even	May	1	4	5
Even	Jun	3	7	8
Even	Jul	12	22	24
Down	Aug	36	70	86
Down	Sep	39	76	114
Down	Oct	82	176	308
Down	Nov	122	408	942
Down	Dec	293	950
Up	Jan	1033
Up		1623	1715	1489

I know it sounds confusing, but its a running 13 month down the side, running 3 month across the top with the two far right columns being "snapshotted" or old data from previous reports.

Currently, I have no real code yet as Im trying to get my head around how this would work, if it can work.

Thanks!!!

[#2]

Thinking outloud here in response to my own question...

Is it possible to have some sort of variable automatically update the inline table when new data comes in?  I guess I can place filters on the report for the date ranges(whether or not people want to see running 3 months going across the top, 6 months or an entire year...give them more flexibility).

Thoughts?

[#3]

Hey Josh,

You mentioned you were getting this data from excel right. So as long as the data is updated in the excel for ever month as per the logic you mentioned then you would have to do nothing. Just keep reading that same table and you should be good. You shouldn't create an inline table because you wouldn't know what the data would be for Jan unless and until its updated in the excel. And then you would have to go and manually change the inline and keep doing this every month.So just directly access the excel and pull in the data.

Is there something else to it that I am missing?

thanks

AJ

[MK_QSL]

You can Create a Pivot or Straight Table with Dimensions as you wish....

Expressions :

For Current Month

Definition

SUM({<Month= {'$(=Month(Today()))'}>}Sales)

Label

=Date(Today(),'MMM-YY')

For Previous Month

Definition

SUM({<Month= {'$(=Month(AddMonths(Today(), -1)))'}>}Sales)

Label

=Date(AddMonths(Today(),-1),'MMM-YY')

And SO ON.......

This will give you Dynamic List of Months Required...

[#5]

Hi,

The table I provided is from an excel that some one puts together.  They put it together from raw data that I provide them(ticket data for problem cases in an IT environment).  I wouldn't be creating anything in QV based on the table that the person creates.  I would be creating the app based on the raw data.

But you are partially correct, maybe I am overthinking this.

I would just need a way to "snapshot" previous data..or a way to note that certain data are from previous runtimes.

I currently export the data from Business Objects...I suppose I could add in a column that shows the runtime date that will show for each record.

Again maybe I am overthinking this.  You got me wondering though

[#6]

I think I kinda understand your situation.

Why dont you use a filter while pulling in the data like

Table:

Load *

From DataBaseTable

where Month >= Addmonths(Month(Today()),-2)

This way everytime it will only pull in current three months of data.

Just a suggestion.

[#7]

The three month restriction for this graph is specific to this graph only.  The rest of the app will need to see data from any and all months.

I think I can skirt around the timeframe part of it.

I just need to be able to show in the graph that in the previous month I there were, for example, 100 tickets open.  Standing inside the month prior to THAT month there were 150 tickets open.  Snapshotted data.

The only way I think I can do this is have one master excel file.  Each runtime I add to the data starting at the first open cell.  Each new set of data will contain a flag for each record showing which runtime it came from...so I can group together prior months.

How does that sound?

[#8]

Thats a good idea. Just make sure the flag doesn't change for the already loaded data. Like for Dec data it should always say Dec and when reading Jan data the flag shouldn't reset the old data's flag to Jan, it should still say Dec and for the new one it should say Jan.

Make sense? Apart from that I think you should be good