Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
Please help stalwar1
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.
I need count(DMU) where SUM(A) > Benchmark..
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.
Please let me know the expression
Can anyone help in this?
Try this?
Count({<Benchmark = {"=Sum(A)>Sum(Benchmark)"}>} DMU)
Or
Count({<Benchmark = {"=Sum(A)>Benchmark"}>} DMU)
Or
Count(If(Sum(A)>=Benchmark, DMU))
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)
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)