topic Re: how to write expression in QlikView

how to write expression

kunkumnaveen — Tue, 04 Jul 2017 15:30:30 GMT

Hello All,

i am able to achieve my requirement partially . the thing is i am trying to display only those materials,spend which has supplied by multiple vendors

Expressions i tried :

1)Sum({<SCOPE={'Yes'},Material={"=Count(Distinct [VENDOR CODE)>1"}>}SPEND)

2)If(Count(distinct TOTAL <Material> [VENDOR CODE])>1,Sum({<SCOPE={'Yes'}>}SPEND))

but both the expressions are giving me list of multiple vendors and single vendors materials , as below

All the 100% rows are single vendor supplied material

so how to not display this 100% rows

Any suggestion plz.....

Re: how to write expression

tajmohamed30 — Tue, 04 Jul 2017 16:07:25 GMT

my suggestion you can do it in dimension limits show values less than 100% in your chart

Re: how to write expression

effinty2112 — Tue, 04 Jul 2017 16:16:15 GMT

Hi Naveen,

Try:

=count(Aggr(if(Count(DISTINCT [VENDOR CODE]) >1,Material), Material))

good luck

Andrew

Re: how to write expression

kunkumnaveen — Tue, 04 Jul 2017 16:25:05 GMT

actually i need to implement it in Qlik sense ,i think in qliksense there is no dimension limits option....have u got any alternative way or expression to achieve this requirement ..pz

Re: how to write expression

kunkumnaveen — Tue, 04 Jul 2017 16:28:59 GMT

hi,thanks for your reply

Actually i am looking for sum of spend

Re: how to write expression

kunkumnaveen — Wed, 05 Jul 2017 05:39:09 GMT

can any suggestion, how to achieve this requirement plz

Re: how to write expression

kunkumnaveen — Wed, 05 Jul 2017 07:39:50 GMT

Still look for the suggestions ,in achieving this requirement

Re: how to write expression

Anonymous — Wed, 05 Jul 2017 07:45:32 GMT

please post a sample...

Re: how to write expression

kunkumnaveen — Wed, 05 Jul 2017 08:02:39 GMT

actually i work in remote server ,so all the app are over there ...can u suggest with any sample expression for this scenario other then i tried plz

Re: how to write expression

effinty2112 — Wed, 05 Jul 2017 08:22:46 GMT

=count(Aggr(if(Count(DISTINCT [VENDOR CODE]) >1,Spend), Spend))

Re: how to write expression

kunkumnaveen — Wed, 05 Jul 2017 08:32:21 GMT

Please find my sample data

Re: how to write expression

effinty2112 — Wed, 05 Jul 2017 08:53:26 GMT

Dear Naven,

Hera are the first few rows of a straight table that may be what you want.

Material	Total	Count of Vendor
	146952889.27	31
0000000A7	13637439.28	4
000A490	668592.72	2
000A760	481471.20	2
000A852	78765439.71	4
000A865	3129844.20	2
000A866	12064547.66	3
00A6156	68062.20	2
00A6360	488119.62	2
0A11775	273441.19	2
0A0012268P04	2926115.31	2
0A0020115E05	33124364.88	3
0A0050857D01	1325451.30	3

Total: aggr(if(Count([VENDOR CODE])>1, Sum(Spend1)),Material)

Count of Vendor: aggr(if(Count([VENDOR CODE])>1, Count([VENDOR CODE])),Material)

Re: how to write expression

kunkumnaveen — Wed, 05 Jul 2017 09:02:54 GMT

Thanks For your kindly help, but walker ,for each material ur showing aggr value what I am looking is individual value for each material rather then aggr value

Re: how to write expression

effinty2112 — Wed, 05 Jul 2017 09:29:05 GMT

Naveen,

Do you want the sum for each vendor for each product?

try:

Material	VENDOR CODE	Expr
		93552942.91
0000000A7	DG028A	4389941.03
0000000A7	DI009	5585682.71
0000000A7	DS093	1649215.70
0000000A7	DS093C	2012599.84
000A490	DP064	650880.72
000A490	PLMA01	17712.00
000A760	DM071	118352.62
000A760	DS109	363118.58
000A852	DD019	15131714.22
000A852	DE004	23883222.60
000A852	DE026	32170113.22
000A852	DS064	7580389.67

Where Expr : aggr(NODISTINCT if(Count(TOTAL<Material> [VENDOR CODE])>1, Sum(Spend1)),Material,[VENDOR CODE])

Re: how to write expression

Kushal_Chawda — Wed, 05 Jul 2017 09:38:11 GMT

try this

=Sum({<Material={"=Count(Distinct {< SCOPE={'Yes'}>}[VENDOR CODE])>1"}>}SPEND)

Re: how to write expression

Kushal_Chawda — Wed, 05 Jul 2017 09:40:16 GMT

sum(aggr(if(Count(Distinct {< SCOPE={'Yes'}>}[VENDOR CODE])>1,SPEND),Material,[VENDOR CODE]))