Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

christian_v
New Contributor

How to group and sum every nth values?

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.

newtable.png

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?

1 Solution

Accepted Solutions

Re: How to group and sum every nth values?

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.


talk is cheap, supply exceeds demand
9 Replies

Re: How to group and sum every nth values?

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!


talk is cheap, supply exceeds demand
christian_v
New Contributor

Re: How to group and sum every nth values?

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?

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.


talk is cheap, supply exceeds demand
christian_v
New Contributor

Re: How to group and sum every nth values?

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?

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.


talk is cheap, supply exceeds demand
stigchel
Honored Contributor

Re: How to group and sum every nth values?

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

christian_v
New Contributor

Re: How to group and sum every nth values?

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.

christian_v
New Contributor

Re: How to group and sum every nth values?

Thanks a lot!

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

stigchel
Honored Contributor

Re: How to group and sum every nth values?

Edited