Need below table with required conditions - Qlik Sense

[shiveshsingh]

Hi Guys

Need your help here. Please check below table.

Sr. No	Month	DMU Names	Location	A	B	C	D	E
1	AUG	Kerala	India	10	232	23	5
2	AUG	Delhi	India	12	45	2	55	67
3	AUG	Noida	India	26	5	3
4	AUG	Lucknow	India		6			43
5	AUG	Delhi	India	13	67	2	54
6	AUG	Delhi	India		68			23
7	AUG	Lucknow	India	14	6	3		23
8	AUG	Kerala	India	177	23	3	5	98
9	AUG	Noida	India	19	332		54
10	AUG	Kerala	India	20			343	9

Through this table, i need below table

Product	Aug		Benchmark
A	2		30
B	3		30
C	1		30
D	2		30
E	3		30

Here bench mark should be editable and dynamic and logic for getting the values below Month(AUG) is

lets take eg for A, so here '2' signifies the count of DMUs whose sum(A) is greater than benchmark.(Kerala and Noida)

Please help, and i don't have license as of now, so kindly help with the expressions and script as well.

[shiveshsingh]

Please help stalwar1‌

[karthiksrqv]

Hi Shivesh,

You can try these steps:

1.

Data:

Load

Sr_No,

DMU,

Location,

'A' as Product,

A as Value

from

[data file](qvd, xml, ooxml etc.. );

2. Concatenate(Data)

Data:

Load

Sr_No,

DMU,

Location,

'B' as Product,

B as Value

from

[data file](qvd, xml, ooxml etc.. );

Repeat this for B, C, D, E

3. Then you can store the benchmark value in a variable created in Qliksense, say v_benchmark

4. Then create a simple table, add the Product as dimension, count(aggr(Sum(Value)>$(v_benchmark),Product)) as your expression. Additonally you can use extensions to make the benchmark editable.

Also, you don't need a license to use Qliksense Desktop. Create a login account on Qlik.com, login and download the Qliksense desktop version.

[shiveshsingh]

I need count(DMU) where SUM(A) > Benchmark..

[karthiksrqv]

Hi Shivesh,

That is the use of the aggr() function in Qlikview/Qliksense. Because you need nested aggregation (calculation of count based on the sum) you need to use aggr.

[shiveshsingh]

Please let me know the expression

[shiveshsingh]

Can anyone help in this?

[Anil_Babu_Samineni]

Try this?

Count({<Benchmark = {"=Sum(A)>Sum(Benchmark)"}>} DMU)

Or

Count({<Benchmark = {"=Sum(A)>Benchmark"}>} DMU)

Or

Count(If(Sum(A)>=Benchmark, DMU))

[shiveshsingh]

Thanks but this is not working,

I need these A, B,C,D,E in dimension ( Product..using crosstable)

and then count(DMU) where sum of values in A column is greater than 'Benchmark'.

something like this..

select count(DMU) where  (sum(Product) group by DMU) > Benchmark.

Product   Data

A               2 ( As sum of A for  kerala and Noida is greater than 30)

[shiveshsingh]

Something like this.. for all the products,

count(Aggr(If(Product = 'A' and Sum(Values)>'30', DMU_NAMES),  DMU_NAMES))

please help in building expression for all the products.( as i have shown in the output table)