Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Partner
Partner

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

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.

8 Replies
MVP
MVP

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

Can you provide the result you are looking for?

Thanks

Partner
Partner

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


MVP
MVP

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.

Partner
Partner

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

Partner
Partner

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.

Highlighted
Partner
Partner

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

Something like in the attached QVW?

Partner
Partner

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.

Partner
Partner

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