How to sort topN side by side bar chart 1

[civiram]

Hi all,

I've created a side by side bar chart which I've plotted the count of records for this week against the previous week.

My data:

Current Week Previous Week
AA 11 4
BB 5 5
CC 6 1
DD 2 2
EE 4 1

Thanks for any help.
My question is how will I sort the chart so that it's sorted using the values in the current week. At this stage, the sorting is based on the count of records for both current and previous week.

So if I'm getting the top 3 record, this should be how the chart will be sorted:

Current Week Previous Week
AA 11 4
CC 6 1
BB 5 5

 

[lbass]

Please explain the setup in the chart expert--type of bar chart, and the "on change of" and summary fields.

-LB

 

[civiram]

Hi,

It's a side by side bar chart.

"on change of"

table.product_type
@currentprevious

under show value - count of table.id

the formula @currentprevious is :

if {TABLE1.STARTDATE}>={?Start Date}
and {TABLE1.STARTDATE}<={?Start Date}+6 then "Current Week"
else
if {TABLE1.STARTDATE}>={?Start Date} -7
and {TABLE1.STARTDATE}<={?Start Date} -1 then "Previous Week"

Thanks for your help.

 

[civiram]

hi,

any idea?

or if this is not possible, what are other alternatives that I can use to achieve the same objective?

thanks.

 

[lbass]

You have to change the set up. Use only table.product_type
as the on change of field. Then create two formulas:

//{@curr}:
if {TABLE1.STARTDATE} in {?Start Date} to
{?Start Date}+6 then
{table.ID} else
tonumber({@null})

//{@prev}:
if {TABLE1.STARTDATE} in {?Start Date}-7 to
{?Start Date}-1 then
{table.ID} else
tonumber({@null})

... where {@null} is a new formula that you open and save without entering anything. Add these as your summary fields, using count as the summary.

Then you can go into the chart expert and select {table.product_type}->topN->All->count of {@curr}->descending.

-LB

 

[civiram]

Your solution worked! Thank you very much.