topic Re: Display values on line chart in QlikView

Display values on line chart

maxtromb — Mon, 08 Jan 2018 10:51:05 GMT

Hi everybody,

I have a line chart with an x-axis time series (which is updated every month with the latest information) and an y-axis with sales values.

The user can select the shop for viewing line chart of its sales value during the month.

Furthermore, each store is associated with events such as openings of competitors whose date is expressed in months like sales values.

I want to show these events with a symbol placed in the month in which it happened at the sales level (plus a million not to overlap the line) of the store of that month.

With this function:

=if(inmonth([DATA IMPATTO],Data,0)=-1,sum(Vendite_AM)+1000000)

where:

DATA IMPATTO is the date of the event (month)

Data is the date of the sales values (month)

Vendite_AM is the sales values

I can display the symbol only if there is a singol event.

If I have more than one event associated to one shop I cannot display anything on the graph, just the line.

How can I do it?

Re: Display values on line chart

Anil_Babu_Samineni — Mon, 08 Jan 2018 13:34:03 GMT

Not sure, I understand this?

=if(inmonth([DATA IMPATTO],Data,0)=-1,sum(Vendite_AM)+1000000)

where:

DATA IMPATTO is the date of the event (month)

Data is the date of the sales values (month)

Vendite_AM is the sales values

Re: Display values on line chart

sunny_talwar — Mon, 08 Jan 2018 13:41:11 GMT

If I have more than one event associated to one shop I cannot display anything on the graph, just the line.

So, if you have more than one event, you would not want to show it on the chart? I think it should be possible, would you be able to share a sample of what you have so far?

Re: Display values on line chart

jonathandienst — Mon, 08 Jan 2018 13:42:39 GMT

Perhaps I am mistaken, but I suspect that DATA IMPATTO is not properly associated with Data in the fact table, so if more than one value exists, it is ambiguous and nothing will be displayed. I would perform the InMonth() in the load script and append the event to the main fact table with an event flag field (such as EvFlag = 0 or 1). Then the expression can simplify to

=(Sum(Vendite_AM) +1E6) *EvFlag

Re: Display values on line chart

maxtromb — Mon, 08 Jan 2018 14:28:01 GMT

No no, I want to display all the events associated to one shop.

The graph I posted at the beginning of the discussion (watch 1st image below) is the case where one shop has only one event associated.

If I select I shop that has more than one event, I cannot display anything:

In the second case I would display 4 triangles.

Re: Display values on line chart

maxtromb — Mon, 08 Jan 2018 14:37:08 GMT

I think the dates are actually associated correctly ('MMM - YY') because the formula works when the value is unique.

With your function, how can I display the symbol at the right level of my x-axis (time series in month)?

Re: Display values on line chart

maxtromb — Mon, 08 Jan 2018 14:41:37 GMT

yeah, correct:

in the first line I copy my function/formula

In 3rd, 4th and 5th line I explain the variables contained in the formula for give you more clearness

Re: Display values on line chart

sunny_talwar — Mon, 08 Jan 2018 16:16:31 GMT

Would you be able to attach a sample to check out what might be going on?

Re: Display values on line chart

maxtromb — Mon, 08 Jan 2018 16:32:50 GMT

I want to see 4 triangles for 4 events.

Now I can see 1 triangle only if I have 1 event.

If I have 2 or more events I cannot see anything,

Re: Display values on line chart

Anonymous — Mon, 08 Jan 2018 16:51:51 GMT

Dual(If(DATA IMPATTO=Max(DATA IMPATTO),Data, ''),Sum(Vendite_AM))

Dual(If(Data=Max(Data), DATA IMPATTO, ''),Sum(Vendite_AM))

Re: Display values on line chart

maxtromb — Mon, 08 Jan 2018 17:09:18 GMT

the first function you post works, but it shows all the events, regardless of the filter in the list box of stores.

Re: Display values on line chart

maxtromb — Fri, 12 Jan 2018 10:03:56 GMT

Hi guys, i've found the answer. I write to let you know.

Not if(sum( but sum(if( ...

The function I've used is:

=avg(if(inmonth([DATA IMPATTO],Data,0)=-1,Vendite_AM))

I replaced sum with avg because, if I have for example 3 events in the same date the symbol is displayed on sum(Vendite_AM) * 3