Pivot table ledger

[dries_clairhout]

Hello,

For a client of mine I have to create a pivot table for a digital ledger.

Here is what needs to happen:

There is a field with 6 numbers (xxxxxx) This field is an accounting number.

Now The client wants a pivot table where the first stage is 1 number, when you fold that open 2 and when you fold that open you have an overview.

Simply put

X

XX

XXXXXX

Is there a way that Qliksense would read the numeric value and divide the lines into the correct category? There are seven.

Here is an example with numbers.

1

10

100564

2

20

200456

3

30

300987

And so on.

Anyone have a clue?

Thanks

[rwunderlich · Accepted solution]

It's easiest to create new fields in the script in this case:

left(Account,1) as Level1,
left(Account,2) as Level2

Then use Level1, Level2, Account as dimensions in your pivot table. 

You could also create Level1/2 as calculated dimensions, but script would be preferable. 

-Rob
http://masterssummit.com
http://qlikviewcookbook.com
http://www.easyqlik.com

[dries_clairhout]

Hello Rob,

I'm trying it now.

So If I put in this simple formula, my Accountnumbers will fall under the different levels themselves?

Thanks

[rwunderlich]

Yes, if you create the new Level1 and Level2 fields from the AccountNumber field, all will fall into the correct level automatically.

-Rob
http://masterssummit.com
http://qlikviewcookbook.com
http://www.easyqlik.com