Total in Line Chart

[#1]

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,

[its_anandrjs]

Put another statement like

=Aggr(Sum(Sales),Country)

[its_anandrjs]

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

[MK_QSL]

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'),

  $(=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.

[#5]

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

[MK_QSL · Accepted solution]

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

[MK_QSL]

=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'),

  $(=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.

[its_anandrjs]

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

Regards