Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
I was wondering if you guys have any idea how i could represent different groups of Data by grouping the same expression.
For example:
I got a table that has the account number and the value on the account.
My current table shows information on different levels (pivot table), and has one expression, that the sum(Value).
This way, when u drill down you get to the account and the value.
I need to group like a range of accounts and show the total of that account, and then another range of accounts with another total, but not in different expresions so i dont have a lot of zeros on the accounts that are not grouped on each expression.
(Table of example)
Currently at the end of the pivot table it looks like:
Acc | Value |
---|---|
11 | 1 |
12 | 1 |
13 | 1 |
21 | 1 |
22 | 1 |
23 | 1 |
31 | 1 |
I want to have:
Acc | Value |
---|---|
11 | 4 |
12 | 5 |
13 | 1 |
Total 1's | 10 |
21 | 3 |
22 | 1 |
23 | 2 |
Total 2's | 6 |
31 | 1 |
Total 3's | 1 |
Grand Total | 17 |
Is there anyway of doing this?
Am I explaining my self correctly?
thanks in advance,
HI Mariana,
Attached is the application with solution of your query.
Regards,
Kamal
Perhaps you can simply create an extra field in the script, i.e. left(Acc,1) as Group, and add that new field as dimension to your pivot table. Or perhaps you want to create something like the P&L statement as described in this blog post: How IntervalMatch Solved My Profit and Loss Dilemma
Like this?
HI Mariana,
Attached is the application with solution of your query.
Regards,
Kamal
thanks this solved it perfectly.
the other solutions where a little more difficult to implement with my data model, thats why i mark you correct answer, but thanks to everyone.
KR