topic Re: Need help in using Aggr Function in QlikView

Need help in using Aggr Function

Wed, 22 May 2013 16:29:31 GMT

Hi All,

I have the sales data at State level and Regions is mapped to state as below

State	Region	Sales
S1	R1	10
S2	R2	20
S3	R1	30
S4	R2	40
S5	R1	50

and i have to show a straight table in qlikview like below

State	State Sales	Regional Sales
S1	10	90
S2	20	60
S3	30	90
S4	40	60
S5	50	90

I created a table with dimension as 'State' and created two expressions as below

1. 'Sum(Sales)' to show State Sales

2. 'Sum(aggr(Sum({<State=>}Sales),Region))' to show Regional sales

But the aggr function is showing aggregated value for one state per region and rest of the values it is showing 0's.

Here is the output am getting

State	State Sales	Regional Sales
S1	10	90
S2	20	60
S3	30	0
S4	40	0
S5	50	0

Is there anything wrong with the expression that i used for Regional Sales? Please somebody help me to get the regional values for every state.

Thanks,

Manu

Re: Need help in using Aggr Function

Gysbert_Wassenaar — Wed, 22 May 2013 16:36:52 GMT

Try: aggr(nodistinct sum(Sales),Region)

Re: Need help in using Aggr Function

Wed, 22 May 2013 16:44:50 GMT

Thanks wassennar, it is working.

Re: Need help in using Aggr Function

Thu, 23 May 2013 07:26:22 GMT

Hi wassenar,

I am facing one more problem, It'll be helpful if you suggest a solution.

I created a bookmark as 'BM01' and i am updating this bookmark on click of a button. For button i added a replace bookmark action.

This functionality is working fine in qlikview desktop but after deploying in server, the bookmark is not getting replaced.

Is there any policy/settigs need to be changed?

Thanks,

Manu

Re: Need help in using Aggr Function

Gysbert_Wassenaar — Thu, 23 May 2013 08:58:27 GMT

The document is always read-only on the server. That means document bookmarks can't be replaced. You'll have to enable Server Bookmarks instead on the server. In the QMC go to Document Properties -> Server -> Server Objects and enable both "Allow Server Objects" and "Allow Server Bookmarks". Users can then create and replace their own bookmarks.

Re: Need help in using Aggr Function

Thu, 23 May 2013 09:35:10 GMT

Both are already enabled but still the Document Bookmarks (BM01) which i created is not getting replaced. More over if you open the application on server, i am not getting options to replace document bookmark, i can only replace 'My Bookmarks'

Actually i created one document bookmark and i wanted user to replace the same bookmark if he wants.

I created a button in the application to replace this document bookmark. Is there any option to replace document bookmarks dynamically?

Re: Need help in using Aggr Function

Thu, 23 May 2013 10:21:32 GMT

Wassenar,

I understood that we can't replace document bookmarks on server, but is there any way to replace server bookmarks on click of button without using macros.

Thanks for your help,

Manu