topic Re: How to group and sum every nth values? in App Development

How to group and sum every nth values?

christian_v — Sat, 16 Nov 2024 11:13:52 GMT

I would like to "collapse/group" every nth values for a dimension and measure. I want the values in every "group" to be summarized as illustrated below.

I need it to be dynamic (work with different group sizes) so it can't be done in the load script.

How can I achieve this?

Re: How to group and sum every nth values?

Gysbert_Wassenaar — Tue, 05 May 2015 08:35:08 GMT

Your screenshots look like they're from Qlik Sense. I don't know how to do this in Qlik Sense yet. But perhaps you can ask your colleague csw who posted this document that addesses this problem for Qlikview: Users can map/group dimensions at run-time!

Re: How to group and sum every nth values?

christian_v — Tue, 05 May 2015 12:02:00 GMT

I can see some similarities with that solution but it's not really what I'm looking for. Yes that image is from Qlik Sense but I was hoping for a generic solution where I could create some kind of dynamic fields using qv script (maybe set analysis or some aggr function?). These fields could then be used in any visualization.

Re: How to group and sum every nth values?

Gysbert_Wassenaar — Wed, 06 May 2015 16:24:45 GMT

I've come up with a possible approach. It's far from ideal, but the best I can think of right now. See attached qvf.

Re: How to group and sum every nth values?

christian_v — Wed, 06 May 2015 19:42:15 GMT

Thank you soo much for taking the time and trying to solve this issue. It's almost exact what I'm after, but I need to be able to solve it without involving this Index field that exist in the app you created. Do you think that it's possible? Mabye using RowNo() or something similar? I need this for an extension I'm creating and therefore I dont have any control over the load script etc to add fields like that.

Thanks!

Re: How to group and sum every nth values?

Gysbert_Wassenaar — Thu, 07 May 2015 08:35:41 GMT

I think something like this should work:

= dual(

chr(ord('$(=minstring(Letter))') + class(ord(Letter)-ord('$(=minstring(Letter))'),$(vSize)))

& ' - ' &

chr(

rangemin(

ord('$(=maxstring(Letter))')

, ord('$(=minstring(Letter))') + class(ord(Letter)-ord('$(=minstring(Letter))'),$(vSize))+$(vSize)-1

)

, class(ord(Letter)-ord('$(=minstring(Letter))'),$(vSize))

)

Note, that you'll need a way to change the value of the variable vSize. That probably means an extension too. Otherwise you'll need a field, but you already mentioned that editing the load script isn't an option.

You can expand on the above by replacing my field Letter with a parameter for a dimension and use the first letter of the field values for the grouping.

Now, make a nice extension out of it. I'll be looking forward to it.

Re: How to group and sum every nth values?

stigchel — Thu, 07 May 2015 09:21:12 GMT

I don't know about Qlik Sense so not sure if you can use this, but I played with this in Qlikview just to see if It could be done and came up with this, not pretty but...

In this case Sales Line is the dimension, vGroup the size of the goup

EDIT: The text part of the Dual was not always correct, corrected the expression

=aggr(Dual(

replace(Mid(Concat({<[Sales Line]>} DISTINCT TOTAL [Sales Line],'@'),

Index('@'&Concat({<[Sales Line]>} DISTINCT TOTAL [Sales Line],'@'),'@',((vGroup)*(ceil(rank(rank(Only({<[Sales Line]>} [Sales Line]),1),1)/vGroup)))-vGroup+1),

Index(Concat({<[Sales Line]>} DISTINCT TOTAL [Sales Line],'@')&repeat('@',vGroup),'@',((vGroup)*(ceil(rank(rank(Only({<[Sales Line]>} [Sales Line]),1),1)/vGroup))))-

Index('@'&Concat({<[Sales Line]>} DISTINCT TOTAL [Sales Line],'@'),'@',((vGroup)*(ceil(rank(rank(Only({<[Sales Line]>} [Sales Line]),1),1)/vGroup)))-vGroup+1)

+1)

,'@',''),

ceil(rank(rank(Only({<[Sales Line]>} [Sales Line]),1),1)/vGroup)

)

,[Sales Line]

)

An example (In qlikview) is attached

Re: How to group and sum every nth values?

christian_v — Thu, 07 May 2015 09:23:52 GMT

This is brilliant!

This is basically data reduction achieved using an expression.

Thank you very much, I wish I had your qlik scripting skills Gysbert

I wanted to use the built-in getReducedData method in Qlik Sense's backend API but it doesn't give what I want (Wrong data from getReducedData method?) so thats why I was seeking another approach like this. Thanks again!

I just hope it works good enough performance wise as well.. but we'll se.

Re: How to group and sum every nth values?

christian_v — Thu, 07 May 2015 09:26:43 GMT

Thanks a lot!

Now I have two solutions which I'll try out and pick the one that works best

Re: How to group and sum every nth values?

stigchel — Thu, 07 May 2015 13:22:40 GMT

Edited