Calculate selected values only in set expression

[ramyasaiqv]

Hi All,

I have a requirement tried to  achieve using set analysis but as i'm not really good at set analysis not able to accomplish. So here i'm please help me

I'm trying to  this calculation

Total = sum({<flag={'Standard'}>}recalculated) + sum(0.4*(Count))-

sum({<flag={'Sponsorship'}>}Total)

(Total ="sum of recalculate when flag= ‘Sponsorship’ + sum of 40% of (count) – sum of Total when flag= ‘Standard’  )

By default the app should aggregate sum (recalculated) +sum(count)+ sum(total) but if the user makes a selection on the Flag value(Sponsorship or standard) it calculate the sum of the selected flag condition

for example if a selection is made on flag value 'standard' then the it should sum ( sum({<flag={'Standard'}>}recalculated) + sum(0.4*(Count)) only.. it should dis regard sum({<flag={'Sponsorship'}>}Total)

or if a selection is made on lag value 'Sponsorship' the expression should sum 

sum(0.4*(Count))-

sum({<flag={'Sponsorship'}>}Total)  excluding sum({<flag={'Standard'}>}recalculated).

I have attached the sample qvw and excel data for reference.  Please help me . Thank you,

[john9inno]

is this what you are after?

sum({1<Flag={'Standard'}>}recalculated) + sum({1}Count*0.4)-sum({1<Flag={'Sponsorship'}>}[Total])

[ramyasaiqv]

Hi John,

Thank you very much for the quick response. Actually the expression should aggregate based on the selections made in the list box. By default it should sum all (both standard and sponsorship+cunt ). But  if a selection is made on one of the values of flag for example 'Standard'  then it  should sum only  standard (recalculated) + count  and sponsor should be excluded i.e 0). Please let me know if it's  not clear. Thank you,

[john9inno]

sum(recalculated) + sum(Count*0.4)-sum({<Flag={'Sponsorship'}>}[Total])

I believe this is the expression, you want

[Anil_Babu_Samineni]

Your total seems this?

sum({<flag={'Sponsorship'}>}recalculated) + (sum(Count) * 0.4)-sum({<flag={'Standard'}>}Total)

[ramyasaiqv]

Hi John,

Here is my expression:

sum({<flag={'Standard'}>}recalculated) + sum(0.4*(Count))-

sum({<flag={'Sponsorship'}>}Total)

i need sum r of recalculated where flag= 'Standard' then sum of  40% of count +  sum of "Total" field where flag='Sponsorship'

But now the requirement is if the user makes a selection on the flag value the calculation should change based on the selection

if selection is made on "standard" then the sum should be  

um({<flag={'Standard'}>}recalculated) + sum(0.4*(Count))-

0 ( because sponsorship flag is not selected)

if selection is made on "sponsorship" then

0( because standard not selected ) + sum(0.4*(Count))-

sum({<flag={'Sponsorship'}>}Total)

[ramyasaiqv]

Hi Anil,

Thank you very much for the response but here is the requirement

My expression:

This expression is correct if i don't make any selections on the flag value(standard or sponsorship)

sum({<flag={'Standard'}>}recalculated) + sum(0.4*(Count))-

sum({<flag={'Sponsorship'}>}Total)

(i need sum  of recalculated where flag= 'Standard' then sum of  40% of count +  sum of "Total" field where flag='Sponsorship' )

But now the requirement is if the user makes a selection on the flag value the calculation should change based on the selection

if selection is made on "standard" then the sum should be  

um({<flag={'Standard'}>}recalculated) + sum(0.4*(Count))-

0 ( because sponsorship flag is not selected)

if selection is made on "sponsorship" then

0( because standard not selected  + sum(0.4*(Count))-

sum({<flag={'Sponsorship'}>}Total)

[john9inno]

Hi Ramya,

you have solved most of it.

just need to change 'flag' to 'Flag'

and one more extra thing is put 1 in set analysis, if you want Count ignore the selection

sum({<flag={'Sponsorship'}>}recalculated) + (sum({1} Count) * 0.4)-sum({<flag={'Standard'}>}Total)

sorry typo

should be

sum({<flag={'Standard'}>}recalculated) + sum({1}(Count)*0.4)-sum({<flag={'Sponsorship'}>}Total)

[Anil_Babu_Samineni · Accepted solution]

I don't think what you are need over here. Off course, Even if you select one flag then it should show Same flag of data point and remaining should be Zero. That case, You should follow something like below. As i understand this

If(GetFieldSelections(Flag)='Sponsorship', Sum({<Flag = {'Sponsorship'}>} Total),0) + (sum(Count) * 0.4)-If(GetFieldSelections(Flag)='Standard', Sum({<Flag = {'Standard'}>} Total),0)

Don't worry about your errors on Expression. Just check whether it is "Expression OK" or not. The error problem is Total. Here, Total consider as Function not the column for recommend try to change the Field from Total to some else

And i would highly recommend to create flag for Flag like below

If(Flag = 'Sponsorship', 1, 0) as Sponsorship,

If(Flag = 'Standard', 1, 0) as Standard

Then use where ever you need these Flag at a time in Set analysis instead of Hard code

[ramyasaiqv]

It worked. Thank you very much Anil. Sure i will create variables. I really appreciate your help