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]

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)

[jyothish8807]

Hi Shivesh,

Use the same expression:

count(Aggr(If(sum(Data) >=30,[DMU Names]),[DMU Names],Product))

And do a cross table and create a field "Product", then you can use "Product as your dimension.

Some thing like this:

INPUTFIELD Benchmark;

A:

CrossTable(Product, Data, 4)

LOAD [Sr. No],

     Month,

     [DMU Names],

     Location,

     A,

     B,

     C,

     D,

     E

FROM

(ooxml, embedded labels, table is Sheet1);

NoConcatenate

B:

Load *,

rowno() as Benchmark;

Load

// [Sr. No],

     Month,

     [DMU Names],

     Location,

     Product,

     Sum(Data)as Data

Resident A

group by

// [Sr. No],

     Month,

     [DMU Names],

     Location,

     Product,

     ;

Drop table A;

[shiveshsingh]

Thanks Jyothish

I also need below rows while calculation, how this can be possible?

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

[jyothish8807]

Try like this, if you know the product grouping:

A:

CrossTable(Product, Data, 4)

LOAD [Sr. No],

     Month,

     [DMU Names],

     Location,

     A,

     B,

     C,

     D,

     E

FROM

(ooxml, embedded labels, table is Sheet1);

NoConcatenate

B:

Load *,

rowno() as Benchmark;

Load

// [Sr. No],

     Month,

     [DMU Names],

     Location,

     Product,

     Sum(Data)as Data

Resident A

group by

// [Sr. No],

     Month,

     [DMU Names],

     Location,

     Product,

     ;

concatenate (B)

  Month,

     [DMU Names],

     Location,

    if( Product ='A' or Product='B','Total AB'............) as Product,

     Sum(Data)as Data

Resident A

group by

// [Sr. No],

     Month,

     [DMU Names],

     Location,

     Product,

Load

Drop table A;

Br,

KC

[jyothish8807]

Check this: Seems to be working for me:

sort:

LOAD * INLINE [

    Product

    A

    B

    Total AB

    C

    D

    Total CD

    E

    Total E

];

INPUTFIELD Benchmark;

Load

'' as Benchmark

AutoGenerate 1;

A:

CrossTable(Product, Data, 4)

LOAD [Sr. No],

     Month,

     [DMU Names],

     Location,

     A,

     B,

     C,

     D,

     E

FROM

(ooxml, embedded labels, table is Sheet1);

NoConcatenate

B:

//Load *,

//

//'' as Benchmark;

Load

// [Sr. No],

     Month,

     [DMU Names],

     Location,

     Product,

     Sum(Data)as Data

Resident A

group by

// [Sr. No],

     Month,

     [DMU Names],

     Location,

     Product,

     ;

Concatenate (B)

//

//Load *,

//

//'' as Benchmark;

Load

  Month,

     [DMU Names],

     Location,

    if( Product ='A' or Product='B','Total AB',if(Product ='C' or Product='D','Total CD','Total E')) as Product,

     Sum(Data)as Data

Resident A

group by

// [Sr. No],

     Month,

     [DMU Names],

     Location,

     Product;

Drop table A;

Drop table sort;

exp1:count(Aggr(If(sum(Data)>=Benchmark,[DMU Names]),[DMU Names],Product,Data))

exp2: Inputsum(Benchmark)

Br,

KC

[jyothish8807]

Pfa sample as well.

[shiveshsingh]

Can we use set expression instead of multiple if in expression?

[shiveshsingh]

And also can u explain the expression also..cz i need to incorporate condition like benchmark is different for every product.