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,

[ramyasaiqv]

Hi Anil,

Thank you for your help my initial testing worked and t's working when i

make selections on the flag but by default it's not working. Please can you

help me writing the expression for default also. By default the expression

shouldn't filter on anything. Here is the expression i wrote but i'm

getting error if you get a chance please can you help me .

(*sum({<sponsor_flag={"Standard"}>}unfilled) +

sum({<sponsor_flag=>}0.4(page))-

sum({<sponsor_flag={"Sponsorship"}>}Total_views)),

(If(GetFieldSelections(sponsor_flag)='Standard', Sum({} Total_views),0))

the one in the bold letters is the expression i wrote and it's showing an

error.

Regards,

Ramya

[ramyasaiqv]

Sorry, some reason the content was not posted correctly. Here is the full text  with complete expression.

(sum({<sponsor_flag={"Standard"}>}unfilled) + sum({<sponsor_flag=>}0.4*(page))-

sum({<sponsor_flag={"Sponsorship"}>}Total_views)),

(If(GetFieldSelections(sponsor_flag)='Standard', Sum({<sponsor_flag = {'Standard'}>} unfilled),0) + (sum({<sponsor_flag=>}page) * 0.4)-If(GetFieldSelections(sponsor_flag)='Sponsorship', Sum({<sponsor_flag = {'Sponsorship'}>} Total_views),0))

the one in the bold letters is the expression i wrote and it's showing an error.

Really appreciate your help.

Thank you,

Ramya

[Anil_Babu_Samineni]

When you talk about Default it should 1 over Set analysis

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

[Anil_Babu_Samineni]

(sum({<sponsor_flag={"Standard"}>}unfilled) + sum({<sponsor_flag=>}0.4*(page))-

sum({<sponsor_flag={"Sponsorship"}>}Total_views)),

(If(GetFieldSelections(sponsor_flag)='Standard', Sum({<sponsor_flag = {'Standard'}>} unfilled),0) + (sum({<sponsor_flag=>}page) * 0.4)-If(GetFieldSelections(sponsor_flag)='Sponsorship', Sum({<sponsor_flag = {'Sponsorship'}>} Total_views),0))

From here, What are you trying with my expression, You want to club over here with IF condition

and you can't aggregation before IF condition in set analysis. You should use with in condition only and your expression seems wrong

From above set analysis works as Static. Like Sum(0.4)*page) -- 0.4 * Page for all values

(sum({<sponsor_flag={"Standard"}>}unfilled) + (sum({<sponsor_flag=>} page) * 0.4)- sum({<sponsor_flag={"Sponsorship"}>}Total_views)), // I am not sure where you are writing this?

But, Here it gives as Sum(Page) -- Will return all values and then it multiply to 0.4

- HTH

[ramyasaiqv]

Anil,

Thank you very much, i really appreciate your response. Yes sum(page) should return all values that part is correct.

The expression you wrote is working perfectly when i make a selection on sponor_flag either sponsorship or standard. There is no issue but when no selection is made it's  not bringing in the correct numbers. So i'm trying to include the expression in bold letters in front of the expression you wrote like below. Again the expression is working fine when selections are made but not working correctly when no selection are made on the sponosr_flag.

(sum({<sponsor_flag={"Standard"}>}unfilled) + sum({<sponsor_flag=>}0.4*(page))-

sum({<sponsor_flag={"Sponsorship"}>}Total_views)), (If(GetFieldSelections(sponsor_flag)='Standard', Sum({<sponsor_flag = {'Standard'}>} unfilled),0) + (sum({<sponsor_flag=>}page) * 0.4)-If(GetFieldSelections(sponsor_flag)='Sponsorship', Sum({<sponsor_flag = {'Sponsorship'}>} Total_views),0))

is the syntax correct? i'm not sure if i can write an expression like that please help me with the syntax. Thank you so much for your time and help.

[Anil_Babu_Samineni]

Your expression is not correct to me. In fact, Qlikview don't have capable of If condition after Sum

Here, We can do something like Sum({<Selections1, Selections2, Selections3>} If(GetFieldSelections(Flag) > 0, Sum(Sales))).

Yes, We can do achieve using GetCurrentSelections()

Like, I am continuing with My Expression again over here

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

Sum(TOTAL Total), -If(GetCurrentSelections(Flag)>0, Sum({<Flag = {'$(=GetFieldSelections(Flag))'}>} Total),

Sum(TOTAL Total))

Here, it works as if no selection it will show total value and selections will happened it will work as i said earlier.

Good Luck

HTH

[ramyasaiqv]

Thank you Anil. I will try this.

Just to confirm here i need to create  variable for the Flag correct?  Thank you very much,

Regards,

Ramya

[Anil_Babu_Samineni]

Yes, As per my experience that is the approach i liked it.

[ramyasaiqv]

Ok Thank you Anil.

[ramyasaiqv]

Anil,

I'm really sorry i'm kind of new to Qlik not really good at a lot qlik functions.

I'm just trying to create a  variable  for  these 2 flags you suggested but not sure how to define them in the script. If you get a chance please can you help me . I'm really sorry.

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

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