topic Re: Replace formulae in QlikView

Replace formulae

soniasweety — Sat, 16 Nov 2024 21:53:18 GMT

Hi all, @ Franky_h79 Andrey_krylov

Sunny Talwar

i am using

below expression its working fine in listbox. where as rate filed is coming from database directly.

=Aggr(Only({<[Rate] = {"<=0.97>=1.03"}>} [ Code]), [ Code]) [listbox expression]

but now the issue is rate column is not coming from db . so we need to caluclate on the fly.

so i used this in the script to caluclate rate

Num([ AmountB]/[ AmountA], '#,##0.00%') as [Rate],

so now my requirment is ... i want to replace the rate with above expression to be used in listbox.

=Aggr(Only({<[Rate] = {"<=0.97>=1.03"}>} [ Code]), [ Code]) -- here how can place rate exression instead of direct rate

thanks

sony

Re: Replace formulae

tresB — Thu, 15 Nov 2018 04:49:23 GMT

Try like:
Aggr(
If([ AmountB]/[ AmountA]<=0.97
and
[ AmountB]/[ AmountA] >=1.03 , [ Code])
, [ Code])

Re: Replace formulae

soniasweety — Thu, 15 Nov 2018 04:52:13 GMT

its showing null 😞

Re: Replace formulae

soniasweety — Thu, 15 Nov 2018 04:52:53 GMT

i need to use that in listbox expression

Re: Replace formulae

tresB — Thu, 15 Nov 2018 04:57:22 GMT

This could be because of granularity mismatch. Try like:

Aggr(
If( Sum([ AmountB]/[ AmountA])<=0.97
and
Sum([ AmountB]/[ AmountA]) >=1.03 , [ Code])
, [ Code])

If even this doesn't help. Try to share your sample app.

Re: Replace formulae

mikevwang — Thu, 15 Nov 2018 04:57:31 GMT

I think it should 'or' instead of 'and' and the Only should be kept in case there is a Code with multiple AmountA or AmountB's.

=Aggr(Only(If([AmountB]/[AmountA]<=0.97 or [AmountB]/[AmountA]>=1.03, [Code])), [Code])

Re: Replace formulae

soniasweety — Thu, 15 Nov 2018 05:59:05 GMT

not working

Re: Replace formulae

soniasweety — Thu, 15 Nov 2018 06:05:28 GMT

hi,Tresesco

PFA. when open the app

right side u can see two listbox one is for red count one is for green count... whenever i select red count list box value it should display red value in table.

Re: Replace formulae

tresB — Thu, 15 Nov 2018 06:27:18 GMT

Let me ask you - why do you want this alternative solution, just because you don't want to calculate Rate at the script, or any other reason?

Re: Replace formulae

soniasweety — Thu, 15 Nov 2018 07:07:10 GMT

its not alternate solution.. because i calucalte in the script.. that field if i use in the table its not giving me correct result.

and that listbox is mapped to table so when i do selections in listbox the table is not showing the coresponding values.

Re: Replace formulae

mikevwang — Thu, 15 Nov 2018 07:08:18 GMT

I'm pretty sure this works:

=Aggr(Only(If([Net Sales Order US Dollar Amount Yotta]/[Net Sales Order US Dollar Amount CDR]<=0.97 or [Net Sales Order US Dollar Amount Yotta]/[Net Sales Order US Dollar Amount CDR]>=1.03, [Business Area Code])), [Business Area Code])

Re: Replace formulae

mikevwang — Thu, 15 Nov 2018 07:26:58 GMT

I think this is what you are looking for if you don't want to reproduce the original listbox.

=Aggr(
If(
SUM([Net Sales Order US Dollar Amount Yotta])/SUM([Net Sales Order US Dollar Amount CDR])<=0.97
or SUM([Net Sales Order US Dollar Amount Yotta])/SUM([Net Sales Order US Dollar Amount CDR])>=1.03,
[Business Area Code]
)
,[Business Area Code],[Fiscal Year Month Code],[Profit Center Level 0 Name]
)

Re: Replace formulae

soniasweety — Thu, 15 Nov 2018 07:28:23 GMT

Hi Tresesco any solution?

Re: Replace formulae

soniasweety — Thu, 15 Nov 2018 07:41:38 GMT

the same expression i want to show in text box ...with the count ?
how can i modify this ?Mikevwang

Re: Replace formulae

soniasweety — Thu, 15 Nov 2018 07:44:26 GMT

thank you Mikevwang list box it is working but in text box its showing wrong count.

the same expresision how can i modify in text object to get the listbox count?

Re: Replace formulae

mikevwang — Thu, 15 Nov 2018 07:54:59 GMT

='# of Match rate Red Count:'& chr(13) &sum( aggr( IF(SUM([Net Sales Order US Dollar Amount Yotta])/SUM([Net Sales Order US Dollar Amount CDR])>0.97 and
SUM([Net Sales Order US Dollar Amount Yotta])/SUM([Net Sales Order US Dollar Amount CDR])<1.03,0,1),[Business Area Code],[Fiscal Year Month Code],[Profit Center Level 0 Name]))

Re: Replace formulae

soniasweety — Thu, 15 Nov 2018 08:27:01 GMT

how to put comments like and helpful ? like earlier community wise ?

Re: Replace formulae

soniasweety — Mon, 19 Nov 2018 10:06:49 GMT

Hi,

Franky_h79

Stalwar1

Mikevwang

i want to show the codes where the match rate is null that codes should show in the listbox how can i modify the expression in listbox?

include - SUM([Net Sales Order US Dollar Amount Yotta])/SUM([Net Sales Order US Dollar Amount CDR]) =null or 0

=Aggr(
If(
SUM([Net Sales Order US Dollar Amount Yotta])/SUM([Net Sales Order US Dollar Amount CDR])<=0.97
or SUM([Net Sales Order US Dollar Amount Yotta])/SUM([Net Sales Order US Dollar Amount CDR])>=1.03 ,
[Business Area Code]
)
,[Business Area Code],[Fiscal Year Month Code],[Profit Center Level 0 Name]

Re: Replace formulae

soniasweety — Mon, 19 Nov 2018 09:20:32 GMT

Hi Tresesco how can i include if match rate is null i want to show that codes in the listbox

Re: Replace formulae

tresB — Mon, 19 Nov 2018 10:24:13 GMT

Sorry, I could not follow your requirement.