SubCounts at change in value of a Dimension

[saskia]

Hi

Is there any way to display a "subcount", sort of like a subtotal which will count the number of rows at a change in a field in a straight table?

e.g.

State     Dept       Sales

WA         06           $50

WA         06           $10

WA         06           $300

           06 Total       3

WA         08           $30

WA         08           $9

           08 Total       2

VIC         06           $18

VIC         06           $100

VIC         06           $32

VIC         06           $1

VIC         06           $150

           06 Total       5

Thanks in advance!

[tresesco]

Why don't you use pivot table instead?

[saskia]

Thanks for your reply

Unfortunately, I don't believe a pivot table will support my sorting requirements. I need to sort by a dimension and then by an expression.

[tresesco]

Could you post a sample data and expected output for that? May be we can give a try and see if that is really not possible in pivot or not.

[saskia]

I'm sorry, I can't do that, it is confidential information. I can try to describe the situation to you.

I have Dept as a dimension

I have Agent as a Dimension also but I need to sort this by putting 'DDD' on the top and from there go in alphabetical order

e.g. 

DDD

AA1

CF7

KG5

ZF2

In order to sort this way, I have created an expression:  Order = if(Agent = 'DDD', 'A00', Agent)

I then sort, first by Dept and then by Order. I also hide the column 'Order', as the agent displayed in place of DDD is now incorrect (Agent is included in the table as a dimension).

Perhaps there is a different way I can do this in a Pivot Table? 

[tresesco]

Can you reproduce and explain your case with a sample data?

[saskia]

Please see attached sample data, thanks for your patience.

I basically just want to add a count at each change in Agent in the table I have.

[tresesco]

Expected exact output?

[saskia]

Something like this

[Brett_Bleess]

I am not sure if I am on the right track or not, but check out the following Design Blog posts, think they may be of some help:

https://community.qlik.com/t5/Qlik-Design-Blog/It-s-all-Aggregations/ba-p/1465669

https://community.qlik.com/t5/Qlik-Design-Blog/Pitfalls-of-the-Aggr-function/ba-p/1463275

https://community.qlik.com/t5/Qlik-Design-Blog/Don-t-get-aggr-avated-using-aggr/ba-p/1464136

Here is the base URL for the Design Blog area in case you want to search further yourself for more posts, there are over 700 in this area and most are how-to related, so very useful area.

https://community.qlik.com/t5/Qlik-Design-Blog/bg-p/qlik-design-blog

Regards,
Brett