topic Re: Show All Values of Bucket in Cross Tab in QlikView

Show All Values of Bucket in Cross Tab

madhur4qlik — Wed, 12 Jul 2017 14:14:41 GMT

Hi All,

I have created a bucket by comparing a measure value (measure1) with below logic and I am using this Bucket as a dimension in pivot table. Now the issue is i am getting only those buckets for which i have some value of measure. Is there a way i can show all values of buckets even if we dont have any measure value. Show All values is coming disabled in this case.

[Bucket Table]:

LOAD * INLINE [

Low,High,Value

0,10,0%-10%

10,20,20%

20,30,30%

30,40,40%

40,50,50%

50,60,60%

60,70,70%

70,80,80%

80,90,90%

90,100,100%

];

Let vLow1 = Peek('Low',0);

Let vLow2 = Peek('Low',1);

Let vLow3 = Peek('Low',2);

Let vLow4 = Peek('Low',3);

Let vLow5 = Peek('Low',4);

Let vLow6 = Peek('Low',5);

Let vLow7 = Peek('Low',6);

Let vLow8 = Peek('Low',7);

Let vLow9 = Peek('Low',8);

Let vLow10 = Peek('Low',9);

Let vHigh1 = Peek('High',0);

Let vHigh2 = Peek('High',1);

Let vHigh3 = Peek('High',2);

Let vHigh4 = Peek('High',3);

Let vHigh5 = Peek('High',4);

Let vHigh6 = Peek('High',5);

Let vHigh7 = Peek('High',6);

Let vHigh8 = Peek('High',7);

Let vHigh9 = Peek('High',8);

Let vHigh10 = Peek('High',9);

Let vBucket1 = Peek('Value',0);

Let vBucket2 = Peek('Value',1);

Let vBucket3 = Peek('Value',2);

Let vBucket4 = Peek('Value',3);

Let vBucket5 = Peek('Value',4);

Let vBucket6 = Peek('Value',5);

Let vBucket7 = Peek('Value',6);

Let vBucket8 = Peek('Value',7);

Let vBucket9 = Peek('Value',8);

Let vBucket10 = Peek('Value',9);

Chart Bucket Formula:

if(Measure1>vLow1 and Measure1<=vHigh1, vBucket1,

if(Measure1>vLow2 and Measure1<=vHigh2, vBucket2,

if(Measure1>vLow3 and Measure1<=vHigh3, vBucket3,

if(Measure1>vLow4 and Measure1<=vHigh4, vBucket4,

if(Measure1>vLow5 and Measure1<=vHigh5, vBucket5,

if(Measure1>vLow6 and Measure1<=vHigh6, vBucket6,

if(Measure1>vLow7 and Measure1<=vHigh7, vBucket7,

if(Measure1>vLow8 and Measure1<=vHigh8, vBucket8,

if(Measure1>vLow9 and Measure1<=vHigh9, vBucket9,

vBucket10

)))))))))

Field " How Often" is in question here..

Present Scenario:

Expected Scenario:

HOW OFTEN	Band	0-10	20-Oct	20-30	30-40	40-50	50-60	60-70	70-80	80-90	90-100
0-10%
20%
30%
40%		2	1	-	-	1	-	1	-	-	1
50%		-	1	1	1	-	1	-	-	-	-
60%
70%
80%
90%
100%		-	-	-	-	-	-	-	1	1	-

Re: Show All Values of Bucket in Cross Tab

sunny_talwar — Wed, 12 Jul 2017 16:27:36 GMT

Would you be able to share a sample to take a look at?

Re: Show All Values of Bucket in Cross Tab

madhur4qlik — Wed, 12 Jul 2017 17:11:28 GMT

Thanks for the reply Sunny.

Here you go.

Regards Madhur

Re: Show All Values of Bucket in Cross Tab

sunny_talwar — Wed, 12 Jul 2017 17:36:43 GMT

Add a new table like this

LOAD * INLINE [

Bucket_Island

10%

20%

30%

40%

50%

60%

70%

80%

90%

100%

];

Chart

Dimension

Bucket_Island

Band

Expression

=Sum(Aggr(If(1/Count(TOTAL <Mandate_Number> Mandate_Number) <= Only({1}Bucket_Island) and 1/Count(TOTAL <Mandate_Number> Mandate_Number) > Above(Only({1}Bucket_Island)), 1), Mandate_Number, Band, Bucket_Island))

Re: Show All Values of Bucket in Cross Tab

madhur4qlik — Wed, 12 Jul 2017 18:24:35 GMT

Sunny,

This is working exactly the way i wanted. Im pretty new to Qlikview so would be great if you if could give explain me the logic behind measure calculation and How Often formula.

Thanks

Madhur

Re: Show All Values of Bucket in Cross Tab

sunny_talwar — Wed, 12 Jul 2017 22:33:08 GMT

How Often is a new field that I created in the script

LOAD * INLINE [

Bucket_Island

10%

20%

30%

40%

50%

60%

70%

80%

90%

100%

];

And I am doing a Cartesian Product to get the solution you are looking to get

Used Aggr() to create a temporary table which includes the Bucket_Island as a dimension. and then I am doing a similar check which you wanted and were doing in your dimension before.

Calculated dimensions are less efficient and the way you did it seemed very complicated....

Re: Show All Values of Bucket in Cross Tab

madhur4qlik — Thu, 13 Jul 2017 15:21:14 GMT

Okeh.

But dont you think we have thousand of Mandates Number than the performance in this solution would be compromised.

Re: Show All Values of Bucket in Cross Tab

sunny_talwar — Fri, 14 Jul 2017 00:27:14 GMT

Definitely will.... what other option do you have

Re: Show All Values of Bucket in Cross Tab

madhur4qlik — Fri, 04 Aug 2017 19:53:16 GMT

Hi Sunny,

Thanks for the help here and solution is working but I am facing one issue is this solution is that if I further drill down by clicking any number in the row where we more than one numbers it give me zero

like this I clicked 2 to see what are these two mandates but t gives me 0

can we change your formula so that when we click 2 it should give me those values.. I am displaying these 2 values in separate data grid

Re: Show All Values of Bucket in Cross Tab

sunny_talwar — Mon, 07 Aug 2017 00:40:33 GMT

Not sure I follow what you want when you click on 2?

Re: Show All Values of Bucket in Cross Tab

madhur4qlik — Mon, 07 Aug 2017 04:06:06 GMT

Ok. Let me explain in more detail way.

I am trying to move forward with the solution you provided but the hurdle I am facing is that we have to drill down the COUNT( a measure which we are trying to show against two dimension) into the details of the entity which we are counting.

So we are showing count as 2 n if we click 2 than it should show me table/chart with names and further details of those 2 entities.

But I understood here that in a cross table/Pivot table in qlikview drill down is possible through Dimensions only( " How OFten" and "Band" in this case not" Mandate Count.

so functionality should be like this :

we get this table as first view:

and when click "2" under Band 0-10 with row as 40% , it should give me the details of those "2" counts as in 2 distinct rows but I am getting 0 rows.

by clicking 2 I am hiding the first table n showing another table with those 2 records but those 2 records are not coming as it shows 0.

Could you please help me on in this. really appreciate your inputs on this.

Workaround to this would I can only sell this chart to business by asking them to select 40% first n than see the 2 rows which is one more extra step for them.

Re: Show All Values of Bucket in Cross Tab

balar025 — Mon, 07 Aug 2017 05:00:45 GMT

Rather than going with this much big formula,

can't we use intervalmatch?

Re: Show All Values of Bucket in Cross Tab

madhur4qlik — Mon, 07 Aug 2017 13:57:24 GMT

we have to calculate this on dynamic basis. Calculation is based on selection of some values in a list box. Plz refer above qvws for more information.

Re: Show All Values of Bucket in Cross Tab

sunny_talwar — Mon, 07 Aug 2017 14:00:46 GMT

Check now