Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Not applicable

Line Chart - Divide Values for all Dates by a Value from ONE Date

Hi,

I am trying to create an indices within a line chart by dividing the values at all dates by a value from a particular date.

I was thinking this would be easy. I tried:

(Avg( MyValue) / Avg({1<MyDateField={"SpecificDateString"}> MyValue)*100

This equation is plotted across a chart with MyDateField as the dimension.

The equation works fine at the point of the SpecificDateString (eg "2009-01-09") however no other values plot on the chart. I assume this is because the chart MyDateField dimension is being selected by the measure expression to the SpecificDateString point and so no other values show.

How can I make the values show for all the dates while being divided by the value from the specific date?

Thank you in advance for any help. This has been a real sticking point for me!

1 Solution

Accepted Solutions

Re: Line Chart - Divide Values for all Dates by a Value from ONE Date

Try this:

(Avg( MyValue) / Avg(TOTAL {1<MyDateField={"SpecificDateString"}> MyValue)*100


If all the values have to be divided by MyValue on 2009-01-09

7 Replies

Re: Line Chart - Divide Values for all Dates by a Value from ONE Date

This is because you are restricting your denominator for the specific date. What exactly are you trying to do?? May be provide some sample data with the expected result.

Best,

Sunny

Not applicable

Re: Line Chart - Divide Values for all Dates by a Value from ONE Date

Thanks Sunni,

Yes its the denominator restricting the date that is my problem, but also necessary for the equation. For an indices you need a specific base date. Therefore I need to retrieve a value from a specific date to divide the value from all dates. Ultimately I would like to use a variable, but for now getting it to work with a hardcoded date would be fine. What would you suggest to stop restricting the chart to a given date. Some sort of chart lookup function perhaps?

Thank you

Re: Line Chart - Divide Values for all Dates by a Value from ONE Date

Try this:

(Avg( MyValue) / Avg(TOTAL {1<MyDateField={"SpecificDateString"}> MyValue)*100


If all the values have to be divided by MyValue on 2009-01-09

lusaxy81
New Contributor III

Re: Line Chart - Divide Values for all Dates by a Value from ONE Date

Hi Mike,

Please see the example below and if it is of any help.

The key is to evaluate the denominator through a variable.

you can get fancy with being able to select the month you want to compare it to in a list box by having an unlinked dimension table and say

{1<Month=p(UnlinkedMonth)>} and then just make sure that your Unlinkedmonth has "always only 1 selected" checked in the settings.

Let me know if you need something different?

Regards,

Gareth

Not applicable

Re: Line Chart - Divide Values for all Dates by a Value from ONE Date

Thanks Sunni! This worked and my frustration is over. Thanks for taking the time to answer

Re: Line Chart - Divide Values for all Dates by a Value from ONE Date

No big deal Mike

I am glad I was able to help.

Best,

Sunny

Not applicable

Re: Line Chart - Divide Values for all Dates by a Value from ONE Date

Thanks Gareth. Sunni's suggestion worked which saved me making an unlinked date data island as you suggested, though the suggestion is appreciated. As a sidenote, I am probably one of many Qlik users who only use .qvf files via Qlik Sense, though again I appreciate the effort!

Community Browser