Problems with a matrix (parallel sale of different items)

[r_wroblewski]

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.

If someone has an idea or advice please let me know.

Best regards,

Ronny

[MK_QSL]

Can you provide the result you are looking for?

Thanks

[r_wroblewski]

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

[MK_QSL]

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.

[r_wroblewski]

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

Hope I will find tomorrow the time to provide one.

Regards,

Ronny

[r_wroblewski]

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.

[kuba_michalik]

Something like in the attached QVW?

[kuba_michalik · Accepted solution]

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.

[r_wroblewski]

Hi Jakub

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

Best regards,

Ronny