pivot-table,qlikview , QlikView : get data filtered in pivot table

QlikView : get data filtered in pivot table


Tag: pivot-table,qlikview

I found a great tutorial for building a nicer (dynamic) multibox, without extensions.

I was able to use it, and I really love it. However, I have an issue: if I use a multibox with a master detail table, then if I filter it from detail then master will automatically selected. The below example shows a normal multibox at the top, and dynamic/pivot multibox at the bottom:

enter image description here

How I can achieve this with my dynamic multibox?

For the label, I use:

=if(IsNull(GetFieldSelections(master)) = -1, 'Master',GetFieldSelections(master))

with this, I can set 'Master' as the label.

I can see data has seen filtered but I didn't find a function to get data that has been filtered to set it in the label, maybe someone here can give me an advice to solve this.


The GetFieldSelections() will only give you the list of explicitly selected items. By selecting items in the detail, you're only selecting from the master implicitly.

Try using Concat() instead. This will give you the list of possible values for a field that isn't being directly selected upon.

Your next big headache will be how to determine when to show 'Master' or not. One possible method is to compare the full list of possible values in Master to the possible list. In the example below, I do this by comparing the possible count of master count(master) with the full list of possible values in master count({1}master).

Combining these, one solution could be:

=if(count(master)=count({1}master),'Master',Concat(master,', '))


VBA to change Pivot Filter in OLAP cube to a range

I have a pivot table that pulls data from an OLAP cube, and I'd like to create a macro to filter a pivot field that contains 'Week of Year' based on the value in another cell, so that I can easily change the time frame of the table. My experience...

Simple pandas pivot table issue

I have a dataframe like below: +-------+--------+----------+---------------+ | | ios_id | video_id | feed_position | +-------+--------+----------+---------------+ | 11995 | user1 | 199 | 7 | | 11996 | user2 | 164 | 18 | | 11997 | user3 | 209 | 1 | | 11998 | user3 | 85 |...

Convert rows to columns in sql server?

I'm looking for an efficient way to convert rows to columns in SQL server, I heard that PIVOT is what I'm searching for, and I'm new to pivot tables. This is my example: ItemID VendorName VendorPrice 122 HP 125.66 122 Apple 130.44 122 Microsoft 134.00 122 IBM 124.90 This is...

Pivot Tables or Group By for Pandas?

I have a hopefully straightforward question that has been giving me a lot of difficulty for the last 3 hours. It should be easy. Here's the challenge. I have a pandas dataframe: +--------------------------+ | Col 'X' Col 'Y' | +--------------------------+ | class 1 cat 1 | | class 2 cat...

What is the difference between pivottable.refreshtable and pivottable.update?

What is the difference between the methods pivottable.refreshtable and pivottable.update? I have been googleling and haven't figured out if pivottable.refreshtable is enough to update a pivottable or if pivottable.update also does something significant....

Qlikview Substring and charindex equivalent to show values after the - character

I have a field which has the values field good - examplea good - exampleb bad - examplep ugly - examplet ugly - exampley I want to only show values after the - character. My example output would be field examplea exampleb examplep examplet exampley In SQL it would be...

How to use PIVOT and JOIN together in SQL Server?

This is table 'VendorItemPricing'. ItemID VendorName VendorPrice 122 HP 215.13 122 IBM 264.41 122 Microsoft 257.65 I used this query to get make rows as columns. Select ItemID, [HP] As HPPrice , [Apple] As ApplePrice, [Microsoft] As MicrosoftPrice, [IBM] As IBMPrice from ( select ItemID,VendorName,VendorPrice from VendorItemPricing where ItemID =...

R: output a pivot-like table with subtotals

I'm trying to make a cross tabulation in R, and having its output resemble as much as possible what I'd get in an Excel pivot table. The objective is to replace a report made manually with Excel and Word with one automated with R Markdown; data wrangling and charts have...

Excel VBA Pivot Cache Type Mismatch Run-Time Error '13'

Thanks for any input on this. I'm trying to make a simple pivot table that is taking data from sheet "5 Month Trending May 15" and putting it onto my Pivot Table sheet called "Errors By Criticality - Pivot". When I try to set the pivot cache range with the...

Pivot Table to Reorganize Data

I have data in the below format However, I need to convert it into the below format using pivot tables or other formuals such as where 2015 row is just split by months (divided by 12). Kindly help me with a logic that I can use. Thanks in advance for...

How to count multiple values in one single cell using Pivot Tables in Excel?

I have this table in a Excel sheet: I would like to create a Pivot Table to have this outcome: Do you guys have any idea how to do it? Thank you!...

Derive two hours time blocks from start and end time fields

I have a table in Microsoft Access that has a list of volunteers for an event. The table contains fields such as name, phone number, etc. There are three additional fields; Assignment which describes what activity the volunteer will be assigned to. For example, parking, front gate, ticket Booth, etc....

How to select SQL Server row values as columns (Dynamic Pivot)

I have one table where we are collecting status data from different sensors. For reporting purposes I would need to group information for each sensor (SGUID) to one row. Each sensor is sending reader (RGUID) and value for reader (VAL). Source table: Goal result: I have heard about pivot's but...

Qlikview Automate past 12 months selection

I have a combo chart which shows the average days it took for a person to pay their bill. The Dimension of the Chart is = [Pay Month Year last 12 months] There are no Dimension limits There is 1 expression which is called Average and its definition is: avg({<...

Qlikview document reload fails due to authorization restrictions on SharePoint 2010

The situation I have created a Qlikview document based on SharePoint 2010 list data. The document is working just fine and I am able to reload data from my desktop, and ready to deploy to our Qlikview server. The problem However, when I run a script ("C:\Program Files (x86)\QlikView\Qv.exe" /r...

Sum Colum case () after pivot

Sorry if I don´t explain very well, and the title isn´t very clear. I´m using SQL Server 2005. I have a query with Pivot that is working fine, but know I must add a new query that get resuls from quarterly. This is my query to get result from Month...

QlikView Resources for Beginner Developer

I am looking to add Qlikview Development to my skill-set. I have a C# and SQl background. Are there any free online resources to getting me going at developer level not end-user? What's the best starting place for me and the level of difficulty involved. I have seen the capabilities...

Qlikview Current selections box to use labels rather than table.fieldnames

In my Qlik View document I want to change the Current Selections information to use the Label applied to the field rather than the table.field format. For example PartsTable.PartNo Would be Part Number ...

How do I sum results of two if statements in the same cell?

I'm looking to have the results of two If statements calculated and added in the same cell. I'm getting #VALUE! error. =IF(ISERROR(GETPIVOTDATA("Sum of CHARGES",'Ship City'!$A$3,"ship_city",$B$7,"carrier_type",$A$27,"INV_month_id",D$6,"INV_year_id",$D$5,"Company Name",$B29)),"",GETPIVOTDATA("Sum of CHARGES",'Ship City'!$A$3,"ship_city",$B$7,"carrier_type",$A$27,"INV_month_id",D$6,"INV_year_id",$D$5,"Company Name",$B29))+IF(ISERROR(GETPIVOTDATA("Sum of CHARGES",'Recipient...

Excel PivotTable; how to show values horizontally

I'm trying to rearrange a pivot table that organizes all values (not sum or other statistic) from an original table. Seems simple but I can't find a way to make it values rather than sums. My original data looks like: Rank Name 1 A 1 B 2 C 2 D...

YoY comparison using QlikView

My data contains daily data from last March till now. I want to compare the sales amount of 2015/4 vs 2014/4 and 2015/4 vs 2015/3. My date format is 01,02,03,04,...,12 for Date_Month, and 2014,2015 for Date_Year. I first create two variables: vCurrentYear=max(Date_Year) vCurrentMonth=Max (Date_Month) Then I create a bar chart...

How to select a particular number in a list box and make the other list box to only display the numbers greater than the selected number in QlikView?

I have two list boxes: Box1 and Box2. Box1 has values 10,20,30,40 etc and Box2 has the same 10,20,30 etc. After creating two separate list boxes for Box1 and Box2 respectively in the dashboard, how do I get to show only the values in Box2 which are greater than the...

how to create Pivot query for this?

I need output like this. In this no limit of shape; for example : [RD],[PC],[EM],[SE],[PR],[CM] My data looks like this : POINTER_RANGE SHAPE_NAME PCS CTS VALUE 0.30-0.39 RD 6 1.826 3503.17 0.40-0.49 RD 3 1.238 2837.62 0.50-0.69 PR 1 0.504 776.16 0.50-0.69 RD 5 2.618 8639.58 0.70-0.89 PC 5 4.109...

How to display comparisons with set expression?

My dataset has WeekEndingDate and Sales. I am displaying a straight table with all the selected data but I need to have another table showing the following: Sales (other columns...) First week : 1,000 Last week : 1,350 Difference : 350 Difference %: 35% My questions: a) Can I have...

(kdb+/q) Pivot table: date in rows, symbols in columns, last price as values

General pivot function provided at http://code.kx.com/wiki/Pivot goes: piv:{[t;k;p;v;f;g] v:(),v; G:group flip k!(t:.Q.v t)k; F:group flip p!t p; count[k]!g[k;P;C]xcols 0!key[G]!flip(C:f[v]P:flip value flip key F)!raze {[i;j;k;x;y] a:count[x]#x 0N; a[y]:x y; b:count[x]#0b; b[y]:1b; c:a i; c[k]:first'[a[j]@'where'[b j]]; c}[I[;0];I J;J:where 1<>count'[I:value G]]/:\:[t v;value F]} given that f and g are defined as f:{[v;P]`$raze each...

Sort entire Pivot table in Excel 2013 by column

I have a Pivot Table with many columns. I want the Pivot the ability to sort one of the columns in a way that the whole column is sorted and not the relative position in the hierarchy. Example: NAME PRODUCT SUM Joe A 400 Joe B 200 Joe B 300...

Excel pie charts from pivot table columns

I have a pivot table from a database that looks something like this: Person A Person B Person C Task A 1 3 Task B 1 2 Task C 2 1.5 1 The number is time spent on a task. If it's blank, the person didn't work on that task....

What is the syntax for pivot tables in Pandas? Docs don't seem to be right?

I might be totally crazy, but I'm reading the docs for pivot_table in Pandas, and even some guides Literally using the example from the docs with my own data: import pandas as pd df = pd.read_csv('data.csv') pivot_table(df, values='D', index=['A', 'B'], columns=['C'], aggfunc=np.sum) Produces an error: pivot_table(df, values='D', index=['A', 'B'], columns=['C'],...

Extract matching data from varying number of row

I am trying to build graphic indicators from a (potentially) large set of data using google-spreadsheet. So far, I've used a pivot table to extract the information from the raw data, and I want to build intermediate tables to calculate the different values I need for my indicators. So far,...

SQL Server table manipulation

I am really new here. Currently working on SQL Server 2012. I have table A of the following structure (records are a subset of the full data table): CREATE TABLE [dbo].[TABLE_A]( [ASSET ID] [float] NULL, [TASK ID] [float] NULL, [IN_YEAR ] [float] NULL, [IN_WEEK] [float] NULL, [FLAG] [nvarchar](1) NULL )...

Pivoting a single field in a table

I have a table of the following structure: Name Type Line UniqueID Key Value I need to select all the fields except UniqueID field and add a key 'UniqueID' and a Value field with the value of the UniqueID to the result set. For example if I have three records:...

What does the SSAS setting None in AggregateFunction do?

As I understand Microsoft's explanation setting the AggregateFunction of a measure to "None" should null out the value unless you are viewing the data at the lowest possible level. (i.e. the data element is a copy of the value in one exact cell in the cube). I've created a fact...

How to construct a pivot table that contains values based on certain conditions

I want to construct a pivot table from a huge data-set. Now I want to construct the pivot table that consists of values based on certain specified conditions, for example, the entries with age>25 will only be populated in the pivot table. How to do that without manually filtering out...

Pivot SQL table (Rows into Columns)

I am currently returning data into a table in my SQL stored procedure. I am trying to pivot the rows into columns, and the columns into rows, but I am struggling to do so as a lot of the tutorials I am following to do this have laid out they're...

VBA - test if a value is a valid selection for a PivotField

For a pivot table (pt1) on Sheet1, I use VBA to change the value of a filter field (filterfield) using the code below. Let's say values for field can be A, B or C Sheets("Sheet1").PivotTables("pt1").PivotFields("filterfield").CurrentPage = "A" Occasionally, and let's say randomonly for the purposes of this question, A, B...

Laravel 4 - Scope to only show rows where another DB has no relational items

I have a cars table, and a car_image table. I want to get all the cars that have NO images in the car_image. In my car model I am making a scope: public function scopeNoImages($query) { return $query-> ?? } How can I create a scope that will show only...

Pivot with ID and Dates

I have this data set and Im trying to create a pivot table for the data below. I having some issues when trying to put in the date for each type across. Member ID Type Date 1 A 12/5/2014 1 b 3/6/2014 2 a 6/9/2015 2 b 3/2/2015 2 c...

Using multiple functions in the same field in QlikView

I want to use multiple functions on a field and store the result into one field, like this: left(Campagne,len(Campagne)-4) and Replace(Campagne,'%2f','/') and PurgeChar (Campagne,'.g.c') as Campagne; How can I do this?...

Qlikview GetFieldSelections Multiple Selection

I have a list box that selects distinct values (one of three) from a listbox. This then filters a chart where I show a bar chart based on sum totals. Currently, it works when one value is selected from the listbox, I require the chart to work with more than...

creating a calculated field in excel pivot table based on an item in a column

Normally calculated pivot table fields in excel (2010) you click: design tab > options and sets > calculated field and if you had three columns: name, sales, cost name | sales | cost josh 10 2 your calculated field might be: profit which would be: = sales - cost. However...

Load multiple Excel sheets using For loop with QlikView

I want to load data from two different Excel files, and use them in the same table in QlikView. I have two files (DIVISION.xls and REGION.xls), and I'm using the following code: let tt = 'DIVISION$' and 'REGION$'; FOR Each db_schema in 'DIVISION.xls','REGION.xls' FOR Each v_db in $(tt) div_reg_table: LOAD...

QlikView : get data filtered in pivot table

I found a great tutorial for building a nicer (dynamic) multibox, without extensions. I was able to use it, and I really love it. However, I have an issue: if I use a multibox with a master detail table, then if I filter it from detail then master will automatically...

If a date is 22/12/2015 and has a value of 10, how to fill the value 10 to all the other dates following 22/12/2015 in qlikview?

I have two tables [ORDERS TABLE] and [PRICE LIST TABLE]. [ORDERS TABLE] contains the fields: OrderDate, ItemCode, Quantity. [PRICE LIST TABLE] contains the fields: Date, ItemCode, Price. Now the [PRICE LIST TABLE] explains the price of certain items during certain dates. It hardly consists of 10 - 15 records. For...

Passing external parameter to QV script

I am a newbie to QlikView and looking for some guidance on how to pass external parameter to qv script i.e qvw file. Below is the scenario on which I am working: We are creating a report for which the source is database and we will be using automation tools...

Pivot Table with multiple rows all having the same level hierarchy

I have imported a bunch of data using PowerQuery into a single table and am building dashboard reporting. I have been using Pivot Tables to build my reports, which has worked fine so far. However, I've come to a point though where I want to simply show the count of...

How to have multiple columns on Pivot tables?

I have a table in my sql server called, VendorItemPricing. select * from VendorItemPricing where ItemID = 122 And this query will give me the below results. ItemID VendorName VendorPrice UpdatedDate ObsoleteItem 122 HP 215.13 2015-05-15 11:55:49.983 0 122 IBM 264.41 2015-05-15 11:56:04.990 0 122 Microsoft 257.65 2015-05-15 11:56:23.963 0...

Qlikview line chart with multiple expressions over time period dimension

I am new to Qlikview and after several failed attempts I have to ask for some guidance regarding charts in Qlikview. I want to create Line chart which will have: One dimension – time period of one month broke down by days in it One expression – Number of created...

Load connecting tables from Cassandra in QlikView with DataSatx ODBC

I am new to both Cassandra (2.0) and QlikView (11). I have two keyspaces (tables) with large amount of data in Cassandra and I want to load them to QlikView. Since I can not load the entire set, filtering is necessary. // In QlikView's edit script ODBC CONNECT TO [DataStax...

Can I use Laravel 5 functionality to save positions per company per user?

This is my first question so please excuse any protocol errors! I am also new to Laravel 5. I have watched the Fundamental video series (especially "syncing tags" and have that working for simple pivot tables) and have searched the forum and further online but am not actually even sure...