topic Re: Total in Line Chart in QlikView

Total in Line Chart

Sun, 13 Apr 2014 18:24:14 GMT

Hi,

I have a Line chart with dimensions are Level,Country and expression is SUM(Sales). I am getting expected output, but my client requirement is he would like to see one more line the chart with total. Pls find the attached qvw, source file and screenshot of the dashboard. I am using personal edition only.

I am not sure how to draw a line to show the total value.

Ex: Level: 1, Country: IN and SUM(Sales): 2854

Level: 2, Country: IN and SUM(Sales): 8706

And wanted to see one more line for IN, the total of 11560 and so on.... (I don't want to do anything in the script and only at the dashboard level)

I am not sure whether it is possible or not?

Thanks,

Re: Total in Line Chart

its_anandrjs — Sun, 13 Apr 2014 18:53:49 GMT

Put another statement like

=Aggr(Sum(Sales),Country)

Re: Total in Line Chart

its_anandrjs — Sun, 13 Apr 2014 18:58:41 GMT

Add another expression like

Dimension1:- Country

Dimension2:- Level

Expression1:- =SUM(Sales)

Expression2:- =Aggr(Sum(Sales),Country)

See the attached snap shot

I suggest if you make your first expression as Bar or Symbol it looks better try that one.

Regards

Re: Total in Line Chart

MK_QSL — Sun, 13 Apr 2014 20:07:17 GMT

Create a Line Chart...

Dimension

1) Country

2) Calculated Dimension

=Valuelist($(=Concat(DISTINCT chr(39)&Level&chr(39),',')),'Total')

Expression

Pick(Match(Valuelist(

$(=Concat(DISTINCT chr(39)&Level&chr(39),',')),'Total'),

sum(TOTAL <Country> DISTINCT Sales),

$(=Concat(DISTINCT 'sum({<Level={' & chr(39) & Level & chr(39) & '}>}Sales)' ,',')))

Hope this helps.

Re: Total in Line Chart

Mon, 14 Apr 2014 14:25:46 GMT

Anand, It works but we miss Levels and Manish's function works well. Manish, Its works as expected and I need to understand about the formula here... Thanks both of you..

Re: Total in Line Chart

MK_QSL — Mon, 14 Apr 2014 14:32:51 GMT

Glad that my answer helps you out.

Kindly close the thread by selecting correct/helpful answer.

I will upload soon about how the formula works..

Re: Total in Line Chart

MK_QSL — Mon, 14 Apr 2014 14:46:02 GMT

=Valuelist($(=Concat(DISTINCT chr(39)&Level&chr(39),',')),'Total')

$(=Concat(DISTINCT chr(39)&Level&chr(39),',')) will give you below Level result

'1', '2'

Now Valuelist('1','2','Total') is your second dimension and Country is your First Dimension

Pick(Match(Valuelist(

$(=Concat(DISTINCT chr(39)&Level&chr(39),',')),'Total'),

sum(TOTAL <Country> DISTINCT Sales),

$(=Concat(DISTINCT 'sum({<Level={' & chr(39) & Level & chr(39) & '}>}Sales)' ,',')))

Pick(Match(ValueList(Exp1, Exp1), Res1, Res2))

Match Function first match Exp1, Exp1 and will give your result 1 or 2

Pick will use Res1 if Match give result 1 and Res2 if Match result is 2.

Bit complicated but if you search

Concat

ValueList

Pick

Match

Functions and learn them, it would be easy to understand.

Re: Total in Line Chart

its_anandrjs — Mon, 14 Apr 2014 14:59:48 GMT

Thanks if my suggestion helps you so mark it help ful.

Regards