Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Showing results for

- Qlik Community
- :
- All Forums
- :
- QlikView App Dev
- :
- Pivot/straight table using multiple dimensions in ...

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page

Showing results for

Not applicable

2012-11-01
10:51 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Pivot/straight table using multiple dimensions in single column

Hi,

I am wondering if this is possible. We are wanting to remake a report that users are having to manually make now days using QlikView, and I am wondering if it is possible to do in a single straight table or pivot table. The problem comes in that the columns are not only based on different dimensions, but can also be different calculations.

The report would have data as follows:

Monthly | ||

Sep-12 | Sep-11 | |

% change in State A subscribers | 10% | 2% |

% change in State B subscribers | 5% | -4% |

% change in State C subscribers | 7% | 8% |

… | ||

# of new subscribers from telemarketing | 50 | 20 |

# of new subscribers from door to door | 40 | 5 |

… | ||

% of new subscribers from telemarketing | 30% | 10% |

% of new subscribers from door to door | 25% | 15% |

… |

So the first 3 lines in the example are a percet change based on locaiton of all subscribers, the next two is the total number of new subscribers based on where they came from, and the next 2 are the percentage of the above 2 numbers.

Is this possible in a straight or pivot table? I am wondering if I am just going to have to seperate the items out into seperate tables for this to work, but thought I would ask the question before doing this. (I.E. have one table for the first 3, another table the next 2, and then a 3rd table for the final 2).

Thanks ahead of time

3,336 Views

1 Solution

Accepted Solutions

Not applicable

2012-11-01
11:16 AM

Author

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

in ur load script, create an inline wizard;

load * inline[

Dim,DimValue

1, % change in State A subscribers

2, % change in State B subscribers

3, % change in State C subscribers

4, # of new subscribers from telemarketing

5, # of new subscribers from door to door

6, % of new subscribers from telemarketing

7, % of new subscribers from door to door

];

now in ur straight table, use DimValue as ur dimension & in expression1(Sep-12) use

if(Dim=1,sum({<ActualDim={Actual Dim value for which u need say A 10%}>} Metric1)/sum({<ActualDim={Actual Dim value for which u need say A 10%}>} Metric2),

if(Dim=2,sum({<ActuaDim={Actual Dim value for which u need say B 5%}>} Metric1)/sum({<ActualDim={Actual Dim value for which u need 5% say B}>} Metric2))

since ur result is in %, so i've used division in expr assuming ur actual expr must be something like this

the if can be expanded to Dim7

Same for expr2

Hope it helps

1,181 Views

3 Replies

Not applicable

2012-11-01
11:16 AM

Author

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

in ur load script, create an inline wizard;

load * inline[

Dim,DimValue

1, % change in State A subscribers

2, % change in State B subscribers

3, % change in State C subscribers

4, # of new subscribers from telemarketing

5, # of new subscribers from door to door

6, % of new subscribers from telemarketing

7, % of new subscribers from door to door

];

now in ur straight table, use DimValue as ur dimension & in expression1(Sep-12) use

if(Dim=1,sum({<ActualDim={Actual Dim value for which u need say A 10%}>} Metric1)/sum({<ActualDim={Actual Dim value for which u need say A 10%}>} Metric2),

if(Dim=2,sum({<ActuaDim={Actual Dim value for which u need say B 5%}>} Metric1)/sum({<ActualDim={Actual Dim value for which u need 5% say B}>} Metric2))

since ur result is in %, so i've used division in expr assuming ur actual expr must be something like this

the if can be expanded to Dim7

Same for expr2

Hope it helps

1,182 Views

Not applicable

2012-11-01
12:02 PM

Author

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

So make sure I understand correctly. In my script I create a seperate table that denotes each row I need (and is not connected to any other tables, which is called a data island correct?). Then my expression is a bunch of if statements that based on which row I am looking at, I calculate a different value/metric.

Correct?

By doing this, will I still be able to filter on other dimension values? I.E. different comapnies. Off hand I think it should because as long as i don't specify a specific one in the expression it should, right?

Thanks

1,181 Views

Not applicable

2012-11-01
12:39 PM

Author

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

yeah right...go ahead....

1,181 Views