topic Re: Calculate selected values only in set expression in QlikView

Calculate selected values only in set expression

ramyasaiqv — Wed, 25 Nov 2020 16:16:04 GMT

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,

Re: Calculate selected values only in set expression

john9inno — Thu, 23 Feb 2017 04:16:48 GMT

is this what you are after?

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

Re: Calculate selected values only in set expression

ramyasaiqv — Thu, 23 Feb 2017 04:39:44 GMT

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,

Re: Calculate selected values only in set expression

john9inno — Thu, 23 Feb 2017 05:02:51 GMT

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

I believe this is the expression, you want

Re: Calculate selected values only in set expression

Anil_Babu_Samineni — Thu, 23 Feb 2017 05:10:02 GMT

Your total seems this?

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

Re: Calculate selected values only in set expression

ramyasaiqv — Thu, 23 Feb 2017 05:19:42 GMT

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)

Re: Calculate selected values only in set expression

ramyasaiqv — Thu, 23 Feb 2017 05:22:00 GMT

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)

Re: Calculate selected values only in set expression

john9inno — Thu, 23 Feb 2017 05:26:15 GMT

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)

Re: Calculate selected values only in set expression

Anil_Babu_Samineni — Thu, 23 Feb 2017 05:38:37 GMT

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

Re: Calculate selected values only in set expression

ramyasaiqv — Thu, 23 Feb 2017 05:55:09 GMT

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

Re: Calculate selected values only in set expression

ramyasaiqv — Tue, 28 Feb 2017 05:44:43 GMT

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

Re: Calculate selected values only in set expression

ramyasaiqv — Tue, 28 Feb 2017 05:50:12 GMT

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

Re: Calculate selected values only in set expression

Anil_Babu_Samineni — Tue, 28 Feb 2017 05:59:25 GMT

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)

Re: Calculate selected values only in set expression

Anil_Babu_Samineni — Tue, 28 Feb 2017 06:04:29 GMT

(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

Re: Calculate selected values only in set expression

ramyasaiqv — Tue, 28 Feb 2017 06:38:11 GMT

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.

Re: Calculate selected values only in set expression

Anil_Babu_Samineni — Tue, 28 Feb 2017 09:55:51 GMT

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

Re: Calculate selected values only in set expression

ramyasaiqv — Tue, 28 Feb 2017 13:24:23 GMT

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

Re: Calculate selected values only in set expression

Anil_Babu_Samineni — Tue, 28 Feb 2017 13:28:28 GMT

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

Re: Calculate selected values only in set expression

ramyasaiqv — Tue, 28 Feb 2017 13:36:50 GMT

Ok Thank you Anil.

Re: Calculate selected values only in set expression

ramyasaiqv — Tue, 28 Feb 2017 13:44:47 GMT

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