Multi-Line Pivot or Straight Table help

[Anonymous]

Hello,

I am attempting to build a table for a client to track their new and lost customer information. I have all of the formulas worked out correctly, but I am struggling to match the table layout that the client has requested.

They would like to report by just one dimension (Year), and have expressions for # of New Customers, # of Dropped Customers, and # of Cancelled Appointments, but would like them to be displayed in a table as follows;

Operating Statistics Table
Description | Month-to-Date | Quarter-to-Date | Year-to-Date |
# New Customers | 130 | 180 | 240 |
# Dropped Customers | 40 | 65 | 120 |
# Cancelled Appts | 10 | 18 | 37 |

Can anyone explain if this format for a table is possible within QlikView?

Many thanks,

-Mark

[#2]

Hello Mark

You should be able to achieve this using a standard pivot table I would have thought, have you tried this?

After the pivot is created (it will by defaut add the date based dimensions next to description, you should just pick up the date dimensions and drag them to the top.

Hope this helps a little, if not, post a sample of the QVW with data and I'll see if I can provide more help.

Nigel.

[johnw]

The quick answer is "yes", but HOW to do it will depend a lot on your data model. Is it possible to post either what a LOAD INLINE would look like for some sample data, or a sample QVW with data in it?

[Oleg_Troyansky · Accepted solution]

Here is an idea for you:

Create a new field called "Date Bucket". THis field can have 3 values - MTD, QTD and YTD. Now, create a new table in which you link your Dates to all 3 buckets. Your table will have 2 files:

DateBuckets:

Date

[Date Bucket]

Once you link all the dates to all 3 buckets, you can simply add [Date Bucket] as a Dimension and have all your formulas calculated as usual. QlikView association logic will do the rest...

good luck!

Oleg