topic Re: External grouping according to fields in App Development

External grouping according to fields

tommyl — Thu, 26 Dec 2019 08:42:00 GMT

Hello experts,

I need your help about creating a grouping in the pivot table.

I have a data like:

Table1:

Load

fileName,

Company,

totalActivity,

totalDuration,

totalcharge,

billedActivity,

billedDuration,

billedCharged

from....

I have a graph like:

FileName	Company	TOTAL	BILLED
totalActivity	totalDuration	totalcharge	billedActivity	billedDuration	billedCharged
....	...	....	..	...	..	..	..

I created a pivot table and created a new table like:

Load * inline[

Category, field

'TOTAL', totalActivity

'TOTAL', totalDuration

'TOTAL', totalcharge

'BILLED', billedActivity

'BILLED', billedDuration

'BILLED', billedCharged

]

This turns out to be far from what i need.

Could you please help me?

Regards,

Re: External grouping according to fields

Anil_Babu_Samineni — Thu, 26 Dec 2019 08:56:50 GMT

What is the expected now?

Re: External grouping according to fields

tommyl — Thu, 26 Dec 2019 09:00:53 GMT

Hello,

How can i add a field with the values 'Total' and 'Billed' so that when i add this dimension to the pivot table as Column, related group will be done over the measures i sent above.

Re: External grouping according to fields

Anil_Babu_Samineni — Thu, 26 Dec 2019 09:28:02 GMT

Like this?

Re: External grouping according to fields

tommyl — Thu, 26 Dec 2019 10:14:25 GMT

Yes, that's the one.

Re: External grouping according to fields

Anil_Babu_Samineni — Thu, 26 Dec 2019 10:41:42 GMT

Check this attachment

If(Match(Category, 'BILLED'), Pick(Match(field, 'billedActivity','billedCharged', 'billedDuration'), Sum(billedActivity), Sum(billedCharged), Sum(billedDuration)),
If(Match(Category, 'TOTAL'), Pick(Match(field, 'totalActivity','totalcharge', 'totalDuration'), Sum(totalActivity), Sum(totalcharge), Sum(totalDuration))))

If(Match(Category, 'BILLED') and Match(field, 'billedActivity'), Sum(billedActivity),
If(Match(Category, 'BILLED') and Match(field, 'billedCharged'), Sum(billedCharged),
If(Match(Category, 'BILLED') and Match(field, 'billedDuration'), Sum(billedDuration),
If(Match(Category, 'TOTAL') and Match(field, 'totalActivity'), Sum(totalActivity),
If(Match(Category, 'TOTAL') and Match(field, 'totalcharge'), Sum(totalcharge),
If(Match(Category, 'TOTAL') and Match(field, 'totalDuration'), Sum(totalDuration)))))))

Re: External grouping according to fields

tommyl — Mon, 06 Jan 2020 09:32:28 GMT

Hello Anil,

I tried your solution but i couldnt manage it. Could you please check below too?:

* I need to keep the table i created in the load editor(with the category and field values), right?

* I need to use either of your expressions as a calculated dimension, right?

* I wrote the dimension names within '', is it correct too?

Re: External grouping according to fields

Anil_Babu_Samineni — Mon, 06 Jan 2020 09:48:02 GMT

* I need to keep the table i created in the load editor(with the category and field values), right?
Right

* I need to use either of your expressions as a calculated dimension, right?
Dim 1 - fileName
Dim 2 - Company
Dim 3 - CATEGORY
-- where as Dim 3 created in script with
Load * Inline [
CATEGORY
BILLED
TOTAL
];
Dim 4 - field

Expression -
If(Match(Category, 'BILLED'), Pick(Match(field, 'billedActivity','billedCharged', 'billedDuration'), Sum (billedActivity), Sum(billedCharged), Sum(billedDuration)),
If(Match(Category, 'TOTAL'), Pick(Match(field, 'totalActivity','totalcharge', 'totalDuration'), Sum (totalActivity), Sum(totalcharge), Sum(totalDuration))))

* I wrote the dimension names within '', is it correct too?
I hope, Second point will concur the same