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

Showing results for

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

Showing results for

Discussion Board for collaboration related to QlikView App Development.

Announcements

QlikWorld 2023, a live, in-person thrill ride. Save $300 before February 6: **REGISTER NOW!**

- Qlik Community
- :
- All Forums
- :
- QlikView App Dev
- :
- Using Pivot Table as Datasource

- 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

2011-08-31
11:11 AM

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

Using Pivot Table as Datasource

Good Evening (also goodmorning, depending on where you live)

i was trying to use the results of formulas and other data manipulations from a pivot table in other calculations.

is it possible to fe. use the results from a pivot or straight table to display a graphic ?

thank you

alex

Thats right, i want to re-use the results i got in a straight/pivot table.

because of the functions used in the table, it generates new results, which i want to use in diagrams or other tables.

this should be done without copying the formula from the table to the new diagram.

Text added by Alexander .

2,988 Views

1 Solution

Accepted Solutions

Not applicable

2011-09-01
08:52 AM

Author

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

I believe that

(2) =sum({<Salesare={(Salesarea)+1}>} balance) // does not work (to compare the current and the next unit)

does not work, because you do not define a salesarea to add one to.

{(Salesarea)+1} this is basiacaly saying (say you have 5 sales areas)

{(1,2,3,4,5)+1} which is what is probably causeing the issue.

So defining the current sales area you can then add 1 to see the next value.

(2) =sum({<Salesare={(3)+1}>} balance)

here you are saying return the balance only if the salesare= 4

Note: If you number 2 was used in a pivot/straight table it may work if the dimension is sales area. Would need to test to see.

766 Views

8 Replies

Not applicable

2011-08-31
02:39 PM

Author

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

Hi Alexander

It's my bad i didn't got you can you be more specific regarding your issue .

you said you want to re-use the results you got in a straight/pivot table to show some other graphic charts or is that totally different ...

thanks

Meher

766 Views

Not applicable

2011-08-31
04:00 PM

Author

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

If you want to know the specific value for a dimension you can use set analysis and use that in other places.

Example:

Top Income Balance

Sales 155

COGS 100

GenAdmin 122

Selling 133

=sum({<Topincome={"Sales"}>} balance)

this would return just 155.

766 Views

Not applicable

2011-09-01
04:39 AM

Author

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

@marcsliving, im not shure if i understand you right

i was thinking about your approach, but im not shure if its possible to use your approach to extract data from a straight table.

its not about trying to extract data from a database table, the goal is to read data results from functions or other data from a straight/pivot table. the data i want to read/use is "produced" in the table.

766 Views

Not applicable

2011-09-01
06:54 AM

Author

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

So you want to use the result of a formula from the pivot in other places correct?

Using set analysis would capture the result of the formula as it would in the table. You just need to specify the dimensions in the formula.

In my example it is all from one table using sum(balance) on the pivot/straight table breaks it down by dimension. I needed to use just the total of Sales in a formula somewhere else. Using set analysis let me specify that I only want the balance if the group is Sales. If you have more than one dimension you can specify it in the set analysis to drill down the results even more.

If I am still not understanding correctly could you give more examples of what you are trying to do?

Not applicable

2011-09-01
08:38 AM

Author

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

thanks for your suggestion.

this function works, but only, if you use 1 condition for choosing the values from the table.

if the condition has to change, the function does not work. f.e.

(1) =sum({<Salesare={"3"}>} balance) //works

(2) =sum({<Salesare={(Salesarea)+1}>} balance) // does not work (to compare the current and the next unit)

so, if the condition has to be a formula itself, this way does not work.

is it possible to use something like that with an function as a condition (like in (2))?

Thank you,

Alex

766 Views

Not applicable

2011-09-01
08:52 AM

Author

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

I believe that

(2) =sum({<Salesare={(Salesarea)+1}>} balance) // does not work (to compare the current and the next unit)

does not work, because you do not define a salesarea to add one to.

{(Salesarea)+1} this is basiacaly saying (say you have 5 sales areas)

{(1,2,3,4,5)+1} which is what is probably causeing the issue.

So defining the current sales area you can then add 1 to see the next value.

(2) =sum({<Salesare={(3)+1}>} balance)

here you are saying return the balance only if the salesare= 4

Note: If you number 2 was used in a pivot/straight table it may work if the dimension is sales area. Would need to test to see.

767 Views

Not applicable

2011-09-01
09:22 AM

Author

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

thanks again for the fast reply

basicly,

(2) =sum({<Salesarea={(3)+1}>} balance) //is the same as using

(2) =sum({<Salesarea={4}>} balance) // right ?

so basicly, its like:

(2) =sum({<Salesarea={fixedvalue}>} balance)

is the following possible ?

(2) =sum({<Salesarea={(CURRENTvalue)+1}>} balance)

so when the table shows salesarea "3", the salesarea+1 in the formula should be "4"

and if the table shows "4" the salesare+1 in the formula should be "5"

766 Views

Not applicable

2011-09-02
08:07 AM

Author

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

i found a solution:

=sum({<Salesarea={$(variable)}>} balance)

im now changing the variable thruough an external function & if necessary, an input field

thank you marcsliving

alex