topic Re: Opening Closing with rangesum for Unique Users in QlikView

Opening Closing with rangesum for Unique Users

rajni_batra — Fri, 17 Jul 2015 08:56:54 GMT

Hi,

I am trying to calculate Opening and closing in front end for diff users but i am doing something wrong ,

its carry forwarding closing of all users and i want to calculate it individually for distinct users.

any help would be appreciated!!!

Thanks,

Rajni

Re: Opening Closing with rangesum for Unique Users

kavita25 — Fri, 17 Jul 2015 11:58:47 GMT

Hi,

PFA.

Its done as per ur requirement. Check it.

Re: Opening Closing with rangesum for Unique Users

rajni_batra — Fri, 17 Jul 2015 12:13:11 GMT

Hi,

Kavita, Thanks for your reply.

i dont want this in back-end that would act static and if i remove any dimesnion from the chart it consolidate the opening and closing or i have to add some more expressions to get min and max of opening and closing.

Any way to do it in front-end.

Refer the file attached.

Thanks

Re: Opening Closing with rangesum for Unique Users

kavita25 — Fri, 17 Jul 2015 12:25:47 GMT

Y do you need to remove the dimensions??

Or even if dimension is removed, how the calculation should flow.?

Re: Opening Closing with rangesum for Unique Users

kavita25 — Fri, 17 Jul 2015 12:42:48 GMT

Hi,

PFA

Done.Check it. Same result is done in front end.

And if you want to remove the dimension you need to remove that field from Aggr function as I have used in the expression.

Hope it helps you now.

Regards,

Kavita

Re: Opening Closing with rangesum for Unique Users

sasiparupudi1 — Fri, 17 Jul 2015 13:44:42 GMT

Hi You need an aggregate function in your opening balance calculation

aggr(rangesum(

above(total sum(Bonus),1,RowNo(total))

,above(total -sum(Real),1,RowNo(total))

),UserID,UniqueID,Type,MoneyFlag, CreatedDateTime)

Re: Opening Closing with rangesum for Unique Users

rajni_batra — Tue, 21 Jul 2015 13:49:07 GMT

Nope, Not working , After removing the date dimesnion and the same from aggr too , its picking up value of previous user.

Re: Opening Closing with rangesum for Unique Users

rajni_batra — Tue, 21 Jul 2015 13:49:20 GMT

No luck

Re: Opening Closing with rangesum for Unique Users

kavita25 — Wed, 22 Jul 2015 06:00:26 GMT

What is the expected output after removing the Date dimension?