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

[#1]

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!

[sunny_talwar]

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

[#3]

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

[sunny_talwar · Accepted solution]

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

[Anonymous]

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

[#6]

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

[sunny_talwar]

No big deal Mike

I am glad I was able to help.

Best,

Sunny

[#8]

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!