Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I'd like to be able to display something like this
Name | Jan | Feb | Mar | Apr | May | Jun | Jul | Aug | Sep | Oct | Nov | Dec |
Name1 | 20 | 9 | 10 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
Name2 | 25 | 10 | 20 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
Name 2 | 30 | 11 | 30 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
Monthly Totals | 75 | 30 | 60 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
Yearly Total | 165 |
I've seen how to create a cross table using the wizard in the Edit Script dialog but this looks like it needs the data already formatted.
Can I create either a chart or table view like this if the data not already formatted and exists is a SQL server table.
I am using the personal edition.
Any advice appreciated
Miguel
Thanks, that looks like it will do what I want...it did take me a long time to get the "hold and drop" to work.
Hello Mark,
The short answer is yes. Although you don't specify a LOAD part in your script, is what actually QlikView will implicitly do when you do any SQL SELECT statement. So I'd create anyway the LOAD part formatting when necessary so QlikView gets all the information ready to load.
By the way, what formatting do you want to do? Something like
CROSSTABLE (ShortMonthName, MonthlyTotal) LOAD *;SQL SELECT Name, Jan, Feb, Mar, Apr, May, Jun, Jul, Aug, Sep, Oct, Nov, DecFROM Database.table;
should work, although you will need to do some cleanup removing the totals.
Hope that helps.
Maybe I should explain a little more, the data in the table is not formatted as a cross table, it is just the raw data, each row would be something like
Name,
Status,
DateCreated,
DateCreatedMMM, /* this is the month extracted from the DateCreated */
DateClosed,
DateClosedMMM, /* this is the month extracted from the DateClosed */
So can I still manipulate data in this format this to create a Crosstable.
Ok. Crosstable is one way of loading information, it's not one way of displaying, my fault.
With your data, load it normally, create a new chart, pivot table, set Name and DateCreatedMMM as dimensions, and Count(Status) as expression.
Accept and close the chart properties dialog and you will see the Names followied by a [+] sign. Expand it to see all available DateCreatedMMM for each Name. Click on the dimension (the label is fine) hold and drag to the top of the chart. You will see a vertical or horizontal blue arrow that signs the orientation of the dimension. At the top of the chart, the blue arrow should be horizonal. Then release, and that's it.
Hope that helps.
Miguel
Thanks, that looks like it will do what I want...it did take me a long time to get the "hold and drop" to work.
Miguel
When I had a closer look at the pivotal table I noticed that it was creating a running total rather than display the actual numbers. It doing this
Name Month Jan Feb Mar
NyName 10 20 30
Rather than
Name Month Jan Feb Mar
MyName 10 10 10
I had a look at the partial sums options but couldn't see any way to stop the table totalling the data this way.