Cumulative distinct bar charts

[#1]

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

[sunny_talwar]

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?

[#3]

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

[reddy-s]

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.

[#5]

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

[reddy-s]

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.

[sunny_talwar]

Is doing this in the script an option?

[#8]

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!

[reddy-s]

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.