Announcements
The way to achieve your own success is the willingness to help somebody else. Go for it!
cancel
Showing results for
Did you mean:
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
MVP

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
Author

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

Master II

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
Author

Hi Sangram,

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!!

Master II

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.

MVP

Is doing this in the script an option?

Not applicable
Author

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!

Master II

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.

Tags
Community Browser