topic Re: Not getting all expression values(Count of Customers) in chart with respect to bucket in QlikView

Not getting all expression values(Count of Customers) in chart with respect to bucket

chandan3535 — Sun, 10 Jan 2016 20:33:40 GMT

Dear Team,

I have a requirement ,i want to achieve count of distinct customers bifurcated on the following conditions-

if(total amount of customer < =30,000 then it should lie in bucket of 0-30,000)

if(total amount of customer > 30,001 and < 70 ,000 then it should lie in bucket of 30,001- 70000)

if(total amount of customer > 70,001 and < 100000 then it should lie in bucket of 70,001 -100000)

if(total amount of customer > 100000 then it should lie in bucket of >100000)

I am able to achieve all count of customer when i am writing the expression on text object , but at the same time when i am putting expression in my chart only first expression value is getting displayed other values are not getting displayed.

I want to achieve all distinct count of customers along the bucket.

Please note i am doing aggregation in my expression as i need to pick only those value only whose Amount i.e. (Out_NetDBMTR) is not equal to zero

Attaching my QVW.

Any help would be greatly appreciated.

Re: Not getting all expression values(Count of Customers) in chart with respect to bucket

oknotsen — Sun, 10 Jan 2016 21:21:25 GMT

Unless you are doing this for a hand full of record, doing a nested if-statement like this in the front-end is both bad for performance and a lot of work.

I would suggest doing an intervalmatch() in your script.

Going to take a look at your QVW now to see if I can give you a fitting example.

Re: Not getting all expression values(Count of Customers) in chart with respect to bucket

oknotsen — Sun, 10 Jan 2016 21:28:12 GMT

Make some table in your script that has the number of Customer per... well... that's the part I did not understand of your data set.

After that, add this code (note that I edited your Amnt_Bckt table a little bit, splitting the minimum and maximum in two fields):

Amnt_Bckt:
LOAD * INLINE [
BucketMin, BucketMax, BucketNo
0, 30000, 1
30001, 70000, 2
70001, 100000, 3
100000, 99999999, 4
];

Join (SomeTable)
IntervalMatch (FieldNameWithNumberOfCustomers)
Load
BucketMin,
BucketMax
Resident SomeTable;

Re: Not getting all expression values(Count of Customers) in chart with respect to bucket

chandan3535 — Mon, 11 Jan 2016 10:08:02 GMT

Dear OVK,

Thanks for your suggestion, i also knew that using nested if else degrade performance , but when you don't have any other option and any how you want to achieve result , sometimes you need to use it, So i used nested if else in my above problem.

I cannot use interval match as i am comparing with the values that are coming at run time based on some condition which i cannot apply on back end.

Trust you have understood why i have used nested if else in my above program.

Re: Not getting all expression values(Count of Customers) in chart with respect to bucket

oknotsen — Mon, 11 Jan 2016 12:37:03 GMT

Sometimes you have to, I totally get that . I hoped you could avoid them, but I guess you cant.

To busy at the moment to see if there is another solution, so I hope for others to jump in this topic.

Re: Not getting all expression values(Count of Customers) in chart with respect to bucket

chandan3535 — Mon, 11 Jan 2016 13:09:15 GMT

Already Solved , i used calculated dimensions with nested if .