topic Help with a line chart? in QlikView

Help with a line chart?

Anonymous — Fri, 13 Oct 2017 15:55:33 GMT

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,

Re: Help with a line chart?

vishsaggi — Fri, 13 Oct 2017 16:06:15 GMT

Can you share some sample data with expected output please?

Re: Help with a line chart?

Anonymous — Fri, 13 Oct 2017 16:24:54 GMT

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.

Re: Help with a line chart?

swuehl — Fri, 13 Oct 2017 16:41:09 GMT

Have you tried with your dimension limits and an expression like

=Sum(TOTAL<Month> Qty)

resp.

=Sum(Qty) / Sum(TOTAL<Month> Qty)

Re: Help with a line chart?

vishsaggi — Fri, 13 Oct 2017 17:09:05 GMT

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?

Re: Help with a line chart?

swuehl — Fri, 13 Oct 2017 17:13:07 GMT

It has dimension limits checked on Item

Re: Help with a line chart?

vishsaggi — Fri, 13 Oct 2017 17:23:19 GMT

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.

Re: Help with a line chart?

Anonymous — Fri, 13 Oct 2017 18:05:26 GMT

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?

Re: Help with a line chart?

swuehl — Fri, 13 Oct 2017 18:09:10 GMT

'Didn't work' is not a good issue description.

Please post a sample QVW and your requested result.

Re: Help with a line chart?

Anonymous — Fri, 13 Oct 2017 18:22:08 GMT

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.

Re: Help with a line chart?

swuehl — Fri, 13 Oct 2017 18:28:59 GMT

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)