Skip to main content
Announcements
Global Transformation Awards! Applications are now open. Submit Entry
cancel
Showing results for 
Search instead for 
Did you mean: 
paulwalker
Creator III
Creator III

Dynamic Group by when user select dimension.

Hi Community,

I have one requirement... I have to implement dynamic grouping script level..

Please find the below code,  by default I have aggregated based on InsCode with Salary....

Suppose if user select Category.. it should be group by based on Category and InsCode..(I have to write many combination group by, if it is dynamic that would be great)

Is there any possibility script level..... I have to reduce the data while selection...

MAIN:
LOAD * INLINE [
CODE, Category, Industry, InsCode, Salary
AAA, Qlik Sense, Banking, FIN.01.005.E4A, 123456
AAA, Qlik Sense, Banking, FIN.01.005.E4A, 5000
ABC, QlikView, Banking, AK.02.001.P40, 400
ABC, QlikView, Banking, AK.02.001.P40, 123
ABC, QlikView, Banking, AK.02.001.P40, 7890
ABC, QlikView, Sales, AK.02.001.P40, 4000
ABC, QlikView, Sales, AK.02.001.P40, 39975
BBB, SWIFT, Sales, AK.02.001.P40, 38000
BBB, SWIFT, Sales, AK.02.001.P40, 3000
XYZ, Spot Fire, Sales, FIN.02.001.P60, 56375
XYZ, Spot Fire, Sales, FIN.02.001.P60, 1350
];

Default group by InsCode..

GROUP:
LOAD InsCode, AVG(Salary) as AVGSAL Resident MAIN
Group BY InsCode;

If user select category, group by should be Category and InsCode

GROUP:
LOAD InsCode, AVG(Salary) as AVGSAL Resident MAIN
Group BY InsCode, Category;

 

Thanks in Advance!

Labels (1)
4 Replies
rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

If this is for the user, why not just use the grouping field as the chart dimension and let the chart do the aggregation. Do you have to aggregate in the script?

-Rob

paulwalker
Creator III
Creator III
Author

Yes @rwunderlich , we have to do scripting level - I have many combinations to do.

I can write simply below expression in UI (which I have highlighted in red below, that should change based on user selections for dimension)

AVG(AGGR(AVG(Salary), InsCode, Category)))

is there anyway to pass variable or script level?

 

rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

I'm sorry, I'm not following what you are trying to do.  Can you post an example of the chart you are trying to create?

I'm not understanding the relationship between a user making a selection and the script.  Script is executed before user interaction. There are some specialized cases like ODAG where user selections are used to generate a new script.  But I'm not seeing that here. 

If you want to allow the user to select the Dimension grouping, could you add the potential dimensions as Alternate DImensions to the chart and let the user select they one(s) they want?  Why rerun script?

Yes, you can dynamically change the Aggr dimension using $().  The exact form would depend on how users "select" the dimension but it could be something like this if the names were values in a field called "Dims".

AVG(AGGR(AVG(Salary), InsCode, $(=Concat(Dims,','))))

-Rob

paulwalker
Creator III
Creator III
Author

@rwunderlich , sorry for the delay response.

comparison using states average calculation is normal,  but when user select first set of selection - what we have data displayed and aggregated data, this data should be apply to StateB set of selections.

each selection level  have to aggregate data, let say if i select Industry data should be aggregate on Industry,

if user select location data should be aggregate on Location... etc I have to create many combinations between selections

paulwalker_0-1617097178964.png

Please find the attachment,