Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
Why don't you use pivot table instead?
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.
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.
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?
Can you reproduce and explain your case with a sample data?
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.
Expected exact output?
Something like this
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