topic Re: Stack Bar Chart - 100% Stack Bar Chart in QlikView

Stack Bar Chart - 100% Stack Bar Chart

ishanbansal1204 — Thu, 29 Sep 2016 12:21:43 GMT

Hi Experts,

Facing a scenario where in i am getting the values in the form of stack bar chart but i want to show that in the form of 100% stack bar chart.

Dimensions - Week and Category

Category has the formula -

IF (Aggr(Sum(AMOUNT_CLIENT), [CLIENTNUMBER],[YEAR],[WEEK]) < 10, 'Low Spender',

IF (Aggr(Sum(AMOUNT_CLIENT), [CLIENTNUMBER],[YEAR],[WEEK]) >= 10 AND Aggr(Sum(AMOUNT_CLIENT), [CLIENTNUMBER],[YEAR],[WEEK]) <25, 'Average Spender',

IF (Aggr(Sum(AMOUNT_CLIENT), [CLIENTNUMBER],[YEAR],[WEEK]) >= 25 AND Aggr(Sum(AMOUNT_CLIENT), [CLIENTNUMBER],[YEAR],[WEEK]) <50, 'Hign Spender',' Very Hign Spender')))

Measure is Count(DISTINCT [CLIENTNUMBER])

These are basically categorized in Bucketing system.

If you need any more details let me know.

Thanks

Message was edited by: Ishan Kumar

Re: Stack Bar Chart - 100% Stack Bar Chart

sunny_talwar — Thu, 29 Sep 2016 16:20:49 GMT

May be try this:

Count(DISTINCT [CLIENTNUMBER])/Count(TOTAL <Week> DISTINCT [CLIENTNUMBER])

Re: Stack Bar Chart - 100% Stack Bar Chart

ishanbansal1204 — Fri, 30 Sep 2016 03:40:14 GMT

Hi Sunny,

I go the values as 400%

may be because i have bucketing involved in this?

Re: Stack Bar Chart - 100% Stack Bar Chart

ishanbansal1204 — Fri, 30 Sep 2016 06:38:02 GMT

Hi Sunny,

It is showing me as 400%.

have checked but the values are not right.

we have bucketing as part of my dimension and i feel that might be creating a problem.

Re: Stack Bar Chart - 100% Stack Bar Chart

ishanbansal1204 — Fri, 30 Sep 2016 08:07:29 GMT

Not Working..

Its showing me in the form of 400%

I think i am using bucketing here so it is taking 100% each for bucketing.

Please suggest further.

Re: Stack Bar Chart - 100% Stack Bar Chart

sunny_talwar — Fri, 30 Sep 2016 15:56:21 GMT

Can you share a sample?

Re: Stack Bar Chart - 100% Stack Bar Chart

rupamjyotidas — Fri, 30 Sep 2016 15:59:35 GMT

Taking help of Sunny Equation here

this will group it more detailed

Count(DISTINCT [CLIENTNUMBER])/Count(TOTAL <Year,Week> DISTINCT [CLIENTNUMBER])

Re: Stack Bar Chart - 100% Stack Bar Chart

ishanbansal1204 — Tue, 04 Oct 2016 03:42:32 GMT

Hi Sunny,

I have attached the sample in the start Bucketing.csv

Please let me know the solution for it.

Re: Stack Bar Chart - 100% Stack Bar Chart

sunny_talwar — Tue, 04 Oct 2016 09:05:42 GMT

See if this is what you wanted Ishan

Count(DISTINCT [CLIENTNUMBER])/Avg(Aggr(NODISTINCT Count(DISTINCT [CLIENTNUMBER]), WEEK))

Re: Stack Bar Chart - 100% Stack Bar Chart

ishanbansal1204 — Tue, 04 Oct 2016 09:18:33 GMT

Hi Sunny,

Yes this is what i wanted.

How did you achieve this?

What should be in the dimension and measure?

Re: Stack Bar Chart - 100% Stack Bar Chart

ishanbansal1204 — Tue, 04 Oct 2016 09:19:14 GMT

Will this work in Qlik sense as well?

Re: Stack Bar Chart - 100% Stack Bar Chart

sunny_talwar — Tue, 04 Oct 2016 09:20:33 GMT

I don't see a reason why it won't.

Dimension

WEEK

IF (Aggr(Sum(AMOUNT_CLIENT), [CLIENTNUMBER],[YEAR],[WEEK]) < 10, 'Low Spender',

IF (Aggr(Sum(AMOUNT_CLIENT), [CLIENTNUMBER],[YEAR],[WEEK]) >= 10 AND Aggr(Sum(AMOUNT_CLIENT), [CLIENTNUMBER],[YEAR],[WEEK]) <25, 'Average Spender',

IF (Aggr(Sum(AMOUNT_CLIENT), [CLIENTNUMBER],[YEAR],[WEEK]) >= 25 AND Aggr(Sum(AMOUNT_CLIENT), [CLIENTNUMBER],[YEAR],[WEEK]) <50, 'Hign Spender',' Very Hign Spender')))

Expression

Count(DISTINCT [CLIENTNUMBER])/Avg(Aggr(NODISTINCT Count(DISTINCT [CLIENTNUMBER]), WEEK))

Re: Stack Bar Chart - 100% Stack Bar Chart

ishanbansal1204 — Tue, 04 Oct 2016 09:28:34 GMT

Thank you Sunny.

Really appreciate your help.

Thank you again

Re: Stack Bar Chart - 100% Stack Bar Chart

sunny_talwar — Tue, 04 Oct 2016 09:30:06 GMT

No problem at all. Let us know how it goes in Qlik Sense.

Best,

Sunny