Query Generator

eliran — Tue, 21 Jun 2011 13:12:08 GMT

Hi all,

I'm interested in giving the user a Query generator for some data.

Let's say I have the following dimension:

Actual, Sales LY, Sales WP, Expected, Delta exp vs LY, Delta exp vsWP

The details the user want to see are: Value, Qty & Avg P for the above dimension except the Delta which he doesn't want to see the avg price.

My first option was, building 16 dimensions (4*3+2*2).

The problem, exporting it to excel and presenting the data is not easy on the eye, no cell merging thus not an option.

Since I'm using Ajax I can't use macro's to interfere with the outcome design.

Second option, Building the above dimension, Building an excel file that will hold each formula for the dimension/expression like the following:

dimension Actual, SalesLy, Sales WP, etc..

qty X Y Z

value X Y Z

avg p X Y Z

I built a pivot table holding 2 dimensions, one is a general cycle and the other is the Actual/Sales/Etc.

Later, I created Variables that will return the value for the selected dimension and implementing it in the table expression.

I have two problems with this approach:

1. I can't choose more than1 dimension, it returns blank in the pivot

2. I got extra columns when I want to see a delta (I want just the qty &value, without the avg price)

Any help will be gladly welcomed,

Eliran.

topic Query Generator in QlikView