Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I am trying to create a line chart that displays by month the top 10 of more requested items. The proble that I am having is that I want to display the 100% by month (besides that the top 10 is not the 100%).
If I use a count or sum and in the dimension limits set the 10 as max numbers will partially work. But I want to have the % of each item of the total of items for each months.
Is there a way to do this or I mixed all and confuse everyone?
Thanks,
That's probably because you are using TOTAL qualifier without the field list:
Count({$<Code= {"=Date#(YRMON, 'YYYY-MM') > MonthStart(Today(), -12)
AND
Date#(YRMON, 'YYYY-MM') <= MonthEND(Today(), -1) "}>} DISTINCT Code)/
Count(Total<MonthDimensionField> {$<Code= {"=Date#(YRMON, 'YYYY-MM') > MonthStart(Today(), -12)
AND
Date#(YRMON, 'YYYY-MM') <= MonthEND(Today(), -1) "}>} DISTINCT Code)
Can you share some sample data with expected output please?
This is a small example:
Month | Item | Qty |
1 | 1 | 60 |
1 | 2 | 8 |
1 | 3 | 15 |
1 | 4 | 43 |
1 | 5 | 6 |
2 | 1 | 13 |
2 | 2 | 25 |
2 | 3 | 30 |
2 | 4 | 6 |
2 | 5 | 62 |
3 | 1 | 26 |
3 | 2 | 38 |
3 | 3 | 25 |
3 | 4 | 19 |
3 | 5 | 4 |
4 | 1 | 50 |
4 | 2 | 62 |
4 | 3 | 32 |
4 | 4 | 28 |
4 | 5 | 15 |
So, I want to display for month the top 3 items, in percentage for the total of that month. For example in the month 1 should display Item 1 (60/132), 3 (15/132), and 4 (43/132), next month should display 2(25/136), 3(30/136) and 5 (62/132).
Please let me know if it's clear now.
Have you tried with your dimension limits and an expression like
=Sum(TOTAL<Month> Qty)
resp.
=Sum(Qty) / Sum(TOTAL<Month> Qty)
Hello Stefan, How did you get the top 3 in your file. When i checked it does not have dimension limits checked. Still it shows Top 3 values?
It has dimension limits checked on Item
So sorry I was blind. Got it. I was about to give him exactly Sum(Total <Month> Qty) but was not checking this tab and was fiddling around.
It's what I need, but instead of a sum, I am trying to counting the codes (I don't have quantity). This is the expression that I have:
Count({$<Code= {"=Date#(YRMON, 'YYYY-MM') > MonthStart(Today(), -12)
AND
Date#(YRMON, 'YYYY-MM') <= MonthEND(Today(), -1) "}>} DISTINCT Code)
I am counting the number of times that one specific code appears and grouping it by status.
In this example I counting how many of them appear in the last 12 months without count current month. As dimension I have the month and the status.
I have tried to use:
Count({$<CaseClosed = {"=Date#(YRMON, 'YYYY-MM') > MonthStart(Today(), -12)
AND
Date#(YRMON, 'YYYY-MM') <= MonthEND(Today(), -1) "}>} DISTINCT CaseClosed)
/
Count(Total{$<CaseClosed = {"=Date#(YRMON, 'YYYY-MM') > MonthStart(Today(), -12)
AND
Date#(YRMON, 'YYYY-MM') <= MonthEND(Today(), -1) "}>} DISTINCT CaseClosed)
But didn't work. Any idea?
'Didn't work' is not a good issue description.
Please post a sample QVW and your requested result.
Didn't work means that there is not any difference between the results of using:
Count({$<Code= {"=Date#(YRMON, 'YYYY-MM') > MonthStart(Today(), -12)
AND
Date#(YRMON, 'YYYY-MM') <= MonthEND(Today(), -1) "}>} DISTINCT Code)
and
Count({$<Code= {"=Date#(YRMON, 'YYYY-MM') > MonthStart(Today(), -12)
AND
Date#(YRMON, 'YYYY-MM') <= MonthEND(Today(), -1) "}>} DISTINCT Code)/
Count(Total{$<Code= {"=Date#(YRMON, 'YYYY-MM') > MonthStart(Today(), -12)
AND
Date#(YRMON, 'YYYY-MM') <= MonthEND(Today(), -1) "}>} DISTINCT Code)
it's displaying the percentage distributed between all months and not by total by month.