Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
r_wroblewski
Partner - Creator III
Partner - Creator III

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.

Example.PNG.png

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

1 Solution

Accepted Solutions
kuba_michalik
Partner - Specialist
Partner - Specialist

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.

View solution in original post

8 Replies
MK_QSL
MVP
MVP

Can you provide the result you are looking for?

Thanks

r_wroblewski
Partner - Creator III
Partner - Creator III
Author

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

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
Partner - Creator III
Partner - Creator III
Author

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
Partner - Creator III
Partner - Creator III
Author

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
Partner - Specialist
Partner - Specialist

Something like in the attached QVW?

kuba_michalik
Partner - Specialist
Partner - Specialist

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
Partner - Creator III
Partner - Creator III
Author

Hi Jakub

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

Best regards,

Ronny