Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
christian_v
Employee
Employee

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?

Labels (1)
1 Solution

Accepted Solutions
Gysbert_Wassenaar

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

View solution in original post

9 Replies
Gysbert_Wassenaar

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
Employee
Employee
Author

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.

Gysbert_Wassenaar

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
Employee
Employee
Author

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!

Gysbert_Wassenaar

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
Partner - Master
Partner - Master

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
Employee
Employee
Author

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
Employee
Employee
Author

Thanks a lot!

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

stigchel
Partner - Master
Partner - Master

Edited