Dynamic Group by when user select dimension.

[paulwalker]

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!

[rwunderlich]

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]

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]

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]

@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

Please find the attachment,