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: 
joeybird
Creator III
Creator III

AGGR across table qliksense expression using + <=

Hi

this following expression works fine as a normal KPI.

Sum({$<RankNo =

{"=rank(Only({$<OrderWeekFlag = {1}, [DeliveryDate] = {'>=$(=vMinDeliveryWeek)'}>

+

<OrderWeekFlag = {1}>}RankNo))

<=

$(=sum({$< [DeliveryDate]= {'>=$(=vMinDeliveryWeek)'} , OrderWeekFlag = {1} >}[HasOrder]))

"},OrderWeekFlag = {1} >}

[HasOrder])

I want it to work in a table , that has dimension of 'Department'

I understand I need to use something like  SUM(AGGR(SUM(Cost), [DEPARTMENT]))

but how use SUM(AGGR(SUM   ... in the expression below

Sum({$<RankNo =

{"=rank(Only({$<OrderWeekFlag = {1}, [DeliveryDate] = {'>=$(=vMinDeliveryWeek)'}>

+

<OrderWeekFlag = {1}>}RankNo))

<=

$(=sum({$< [DeliveryDate]= {'>=$(=vMinDeliveryWeek)'} , OrderWeekFlag = {1} >}[HasOrder]))

"},OrderWeekFlag = {1} >}

[HasOrder])

I have tried with no joy

update:

Sum(Aggr(Sum({$<RankNo =

{"=rank(Only({$<OrderWeekFlag = {1}, [DeliveryDate] = {'>=$(=vMinDeliveryWeek)'}>

+

<OrderWeekFlag = {1}>}RankNo)), [Department]))

<=

$(=sum(aggr(sum({$< [DeliveryDate]= {'>=$(=vMinDeliveryWeek)'} , OrderWeekFlag = {1} >}

"},OrderWeekFlag = {1} >}

[HasOrder]), [Department]))

/ Sum(AGGR(sum({$< OrderWeekFlag = {1} >}[HasOrder]), [Department]))

this is how close I have now got with no joy

Qlik does not see to aggregate sums well across dimensions

please help

4 Replies
michaelsikora
Contributor III
Contributor III

Joanna,

Maybe try to load the two set analysis as variable. You can do this by going to variable overview in the settings tab. Then you click add variable. So you will have something like the following:

Sum({$<

Variable1

<=

Variable2>}

[HasOrder])

joeybird
Creator III
Creator III
Author

Hi

I don't understand sorry?

please help

Kind Regards

michaelsikora
Contributor III
Contributor III

Hi,

Go to settings and variable overview. Add variable for each set analysis you have. Then you can use the two variables in your set analysis. You cannot have set analysis within set analysis unless you create it as variable.

joeybird
Creator III
Creator III
Author

Hi

here is an updated expression

Sum(Aggr(Sum({$<RankNo =

{"=rank(Only({$<OrderWeekFlag = {1}, [DeliveryDate] = {'>=$(=vMinDeliveryWeek)'}>

+

<OrderWeekFlag = {1}>}RankNo)), [Department]))

<=

$(=sum(aggr(sum({$< [DeliveryDate]= {'>=$(=vMinDeliveryWeek)'} , OrderWeekFlag = {1} >}

"},OrderWeekFlag = {1} >}

[HasOrder]), [Department]))

/ Sum(AGGR(sum({$< OrderWeekFlag = {1} >}[HasOrder]), [Department]))

please help