8 Replies Latest reply: Nov 7, 2014 12:12 AM by Ronny Wroblewski

Problems with a matrix (parallel sale of different items)

Dear Community

I try to create a matrix, which should show the percentage of sales (e.g. Category A & B items) / sales (e.g. Category A)

I have a bill no. field which is connected to the item categorie field and I try to create a straight table where I use the Category field as dimension and a formula for each column.

My problem is how can I use Aggr function to count only bill no. which include both categories.

I tried something like Aggr ???  / Count(Distinct {\$<CATEGORY={'A'}>} BILLNO) *100 but I couldn't find a solution.

Best regards,

Ronny

• Re: Problems with a matrix (parallel sale of different items)

Can you provide the result you are looking for?

Thanks

• Re: Problems with a matrix (parallel sale of different items)

Hi

e.g.

if Category A items were sold 100 times (unique bill no.) and

this 100 times also 20 times Category B items and 50 times Category C items were sold.

the matrix should show following results:

Category     A          B          C

A               100%

B               20%

C               50%

Regards,

Ronny

• Re: Problems with a matrix (parallel sale of different items)

Can you provide some sample data to work around? Can't identify your actual requirements.

If possible, please provide the result which you are looking for.

• Re: Problems with a matrix (parallel sale of different items)

Sorry at the moment I can't provide a sample qvw.

Hope I will find tomorrow the time to provide one.

Regards,

Ronny

• Re: Problems with a matrix (parallel sale of different items)

Hi Community

please see attached test qvw to play around.

I hope now it is easier to understand what I try to achieve.

Regards,

Ronny

PS: In the final application these fields are  in different tables.

• Re: Problems with a matrix (parallel sale of different items)

Something like in the attached QVW?

• Re: Problems with a matrix (parallel sale of different items)

I have a solution based on set analysis and P() element function. I don't really like it because you need a separate expression for every category (although I guess that's how you planned to do it from the start ), so maybe someone can come up with something better. I have included a version where the formula is created based on a parametrized variable, to minimize amount of copypasta.

• Re: Problems with a matrix (parallel sale of different items)

Hi Jakub

Thanks a lot to share your knowledge. Your solution works fine for me.

Best regards,

Ronny