Pivot Table Display

[PradeepReddy]

Hi all,

Good day..

My data looks like below.

CustID,Parent_CustID,Measure1,Measure2,Country
Cust1, Cust1, 10, 20, Country1
Cust2, Cust1, 11, 21, Country2
Cust3, Cust1, 12, 22, Country2
Cust4, Cust4, 13, 23, Country3
Cust5, Cust6, 14, 24, Country4
Cust6, Cust6, 15, 25, Country4
Cust7, Cust7, 16, 26, Country4
Cust8, Cust7, 17, 27, Country5
Cust9, Cust9, 18, 28, Country6

Requirement:

I have a list box with CustID for selections. 

I have pivot table with dimensions.. Parent_CustID & CustID in same order; Expressions: Sum(Measure1)... 10 measures.

When I select any value from List box(CustID), the pivot table should showcase all the customers with in the group(Parent_CustID).

Expected Output:

1) When I select Cust2 in the list Box, the pivot table has to show below info.

CustID,Parent_CustID,Measure1,Measure2
Cust1, Cust1, 10, 20
Cust2, Cust1, 11, 21
Cust3, Cust1, 12, 22

2) When I select Cust4 in the list Box, the pivot table has to show below info.

CustID,Parent_CustID,Measure1,Measure2
Cust4, Cust4, 13, 23

as there are lot of expressions/measures, I am trying to restrict the data at dimension level.

Edited:  Dashboard contains a lot of filters and other charts. The above stated scenario applies to only one pivot chart. In rest of the charts/filters, should show normally i.e. when I select Cust2 , it should reflect Cust2 related data only. To illustrate this I have added one more dimension Country to the data sample.

Thanks in advance

[jyothish8807]

Hi Pradeep,

Try this:

A:
LOAD * Inline [
CustID,Parent_CustID,Measure1,Measure2
Cust1, Cust1, 10, 20
Cust2, Cust1, 11, 21
Cust3, Cust1, 12, 22
Cust4, Cust4, 13, 23
Cust5, Cust6, 14, 24
Cust6, Cust6, 15, 25
Cust7, Cust7, 16, 26
Cust8, Cust7, 17, 27
Cust9, Cust9, 18, 28
];

Left join
B:
Load
distinct
Parent_CustID,
AutoNumber(Parent_CustID) as ID
resident A;

Left join
Load
ID,
CustID as NewCustID
resident A;

[jyothish8807]

Just use 'NewCustID' in the list box and 'CustID' in pivot as dimension 🙂

[PradeepReddy]

thanks jyothish for quick reply.
I have given sample data and this is small part of data model.
Actual table contains around 1million records and each group contains on average 15-20 customers. If we do the join it will impact the size of the app and RAM usage.

So trying achieve with existing table structure as given, with slight modifications.

[jyothish8807]

Hi Pradeep,

From this logic we are creating only one new field 'NewCustID' also since the values are same as CustID it will not increase the size of the app much.

It will increase the load time for sure 🙂

[PradeepReddy]

Its correct, the reload time will increase and a small amount of application size.
But if we did not select any NewCustID(from your solution) the measures will show wrong numbers. To avoid this we have to change the simple expressions to little complex expressions(10 expressions), which in turn leads to performance, on each other selections
And also as per your suggested process, we have to choose 'always one NewCustID' in order to show the correct numbers. Which is not the scenario..

sincerely appreciate your efforts..

[jyothish8807]

Hi Pradeep,

Just removed the left join, now the sum will be correct 🙂

A:
LOAD * Inline [
CustID,Parent_CustID,Measure1,Measure2
Cust1, Cust1, 10, 20
Cust2, Cust1, 11, 21
Cust3, Cust1, 12, 22
Cust4, Cust4, 13, 23
Cust5, Cust6, 14, 24
Cust6, Cust6, 15, 25
Cust7, Cust7, 16, 26
Cust8, Cust7, 17, 27
Cust9, Cust9, 18, 28
];

Left join
B:
Load
distinct
Parent_CustID,
AutoNumber(Parent_CustID) as ID
resident A;

Left join
Load
ID,
CustID as NewCustID
resident A;

[shreya_nadkarni]

superb (y)

[PradeepReddy]

if we take other filter/chart, when we select the data from NewCustID, it will give you wrong results.

Might be I missed to mention in my earlier post, there are other filters/ charts in the dashboard.

to illustrate the same, I have added one Country field, please see the attachment.

[PradeepReddy]

I have to use one list box only to select the customer, either NewCustomerID or Cust_ID