Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Not applicable

Cumulative distinct bar charts

Hi, I am trying to plot 2 charts.

(1) A cumulative distinct bar chart for 5 days.

(2) A bar chart that shows number of new customers per day.

I have given some sample data and expected results shown below. Can please advise how I should do it? Thanks in advance!!

Day 1    Customer    Expected Results for (1)    Expected Results for (2)

                    A                        2                                                2

                    B

Day 2    Customer    Expected Results for (1)    Expected Results for (2)

                    A                    3 (2+1)                                          1

                    B

                    C

Day 3    Customer    Expected Results for (1)     Expected Results for (2)

                    A                    4 (3+1)                                          1

                    B

                    D

Day 4  Customer    Expected Results for (1)       Expected Results for (2)

                    A                          4                                            0

                    B

                    C

                    D

Day 5  Customer    Expected Results for (1)        Expected Results for (2)

                    A                    5 (4+1)                                          1

                    B

                    C

                    D

                    E

8 Replies

Re: Cumulative distinct bar charts

Would you be able to share the raw data behind this chart you are looking for? Do you have something like this?

Day     Customer

1,          A

1,          B

2,          A

2,          B

2,          C

3,          A

3,          B

3,          C

3,          D

and so on?

Not applicable

Re: Cumulative distinct bar charts

The raw data is like this. How do you suggest I should plot the charts? Can it be done by using expressions in the measure?

Day     Customer

1,          A

1,          B

2,          A

2,          B

2,          C

3,          A

3,          B

3,          D

4,          A

4,          B

4,          C

4,          D

5,          A

5,          B

5,          C

5,          D

5,          E

reddys310
Honored Contributor II

Re: Cumulative distinct bar charts

Hi Andrea,

You can make use of these to achieve what you are looking for and is totally possible using expressions in the front end:

rangesum( above( count(Customer),0,rowno()) )

and

for the second one, its better doing it in the load script, by just flagging the new customer and then:


sum(new_customer_flag) this should give you the break down per day.


Thanks,

Sangram.

Not applicable

Re: Cumulative distinct bar charts

Hi Sangram,

Thanks for your reply.

I tried the expression you have given, it gives a cumulative chart. But I'm looking for something slightly different. In other words, for each day, I find out number of new customers, and sum up with that value for the previous day.

Refer to my sample data in my original post. If I were to plot using your expression, the result for Day 2 would be 5. However, my expected result is 3 because there are 2 new customers on Day 1 and only 1 new customer on Day 2.


Hope to hear from you again. Thanks!!

reddys310
Honored Contributor II

Re: Cumulative distinct bar charts

Hi Andrea,

In that case, its better of having flags in the load script and then simply use the sum() function as the expression in the front end.

Thanks,

Sangram.

Re: Cumulative distinct bar charts

Is doing this in the script an option?

Not applicable

Re: Cumulative distinct bar charts

Hi Sangram,

I do not quite understand, could you please elaborate more on having flags in the load script? How do I go about doing that?

Thanks!

reddys310
Honored Contributor II

Re: Cumulative distinct bar charts

Hi Andrew,

Here as you the data comes in every day, you can make use of this:

[dayx]:

load distinct [Todays Customers] ,

                  day,

                  1 as flag

resident [Todays data]

where not exists([Prev Customers],[Todays Customers]);

Now as this would be an incremental load you can create a table by appending the new customers every day.

Thanks,

Sangram.

Community Browser