LINE CHART WITHOUT DIMENSION

[AriIt]

Hi Guys!

I'm new to Qlicksense. I would like to make a line chart but I don't have the dimension.

On my DB I have the following columns:

municipality, water investment 2017,water investment 2018, water investment 2019|sewer investment 2017,sewer investment 2018,sewer investment 2019| depuration investment 2017, depuration investment 2018, depuration investment 2019.

I would a linear chart that has three lines on the Y axis (water,sewer and depuration), and I would 3 years (2017,2018,2019) on the X axis. 

I have the municipality because on my dashboard I have a filter box to select a specific municipality.

Thanks for the help!

[tm_burgers]

I think that you need to look into the CrossTable() function to translate your Data into usable Yearly Data Points. 

ie. your finished table should look like this:

Municipality	Dimension	Year	Value
London	water investment	2017	xxxx
London	water investment	2018	xxxx
London	water investment	2019	xxxx
London	sewer investment	2017	xxxx
London	sewer investment	2018	xxxx
London	sewer investment	2019	xxxx
London	depuration investment	2017	xxxx
London	depuration investment	2018	xxxx
London	depuration investment	2019	xxxx

[Anil_Babu_Samineni · Accepted solution]

May be use Trellis Chart after your model ready?

[AriIt · Accepted solution]

I solved this problem: I created a personalized dimension and measure. 

Dimension:

=ValueList('2017','2018','2019')

Measure:

=If(ValueList('2017','2018','2019')='2017', Sum([Water investment 2017]),

If(ValueList('2017','2018','2019')='2018', Sum([Water investment 2018]),

If(ValueList('2017','2018','2019')='2019', Sum([Water investment 2019]))))

and then another 2 measures for sewer and depuration.