Pivottable to diagram (rangesum)

[#1]

Hello,

I have two problems with the visualization of a forecast - diagram.

The first one:

In the pivottable everything is correctly. Here I'm using following formel:

Rangesum(Before($(vAVG)/10 ,0,ColumnNo())))

In the diagram I'm using this formel and it shows me only the exist months:

if( Month<month(Today()), sum( $(vStd)),

Rangesum(Before($(vAVG) ,0,ColumnNo()))

)

The second problem, if I set the filter on Technology, for example "BI", then I get no values for the future month in diagram and also pivottable.

Can you please help me.

[sunny_talwar]

How is vStd defined here? Would you be able to share a sample?

[#3]

Hi Sunny, vStd is defined: Std/($(vShowHour)) and $(vShowHour) as number

[sunny_talwar]

Just out of curiosity, what do you get when you use this:

Sum($(vStd))

[#5]

If I do it in the formel of diagram, this diagram doesn't change anything. See the screenshot.

If I give this formel Sum($(vStd))in the textbox, then I get the whole amount -> correct.

[sunny_talwar]

Would you be able to share a sample to look at?

Preparing examples for Upload - Reduction and Data Scrambling

[#7]

Hi Sunny,

sorry for my delayed reply. I attached the QV sample. Please look on this.

How I have to handle formel in diagram ?

and why if I select an application, then I get no values shown for the FC month in diagram and also in the table?

[sunny_talwar · Accepted solution]

Try this expression out:

If(Month < Month(Today()),

Sum($(vStd)),

Rangesum(Above($(vAVG) , 0, RowNo())) + Avg({1}0)

)

[#9]

many Thanks! It works! Can you please explain me this :   Avg({1}0) If I want to have total sum of the month, I can use also this formel. I need only click in the diagram on the "accumulation" Jan = 172 Feb = 172 +178 Mrz = 172+178+111 ...

[sunny_talwar]

Avg({1}0) allows us to keep the forecasted area of the chart disallowing any selection made to that part due to {1}.

Is the accumulation part sorted out? or is that a question for me?

Also, Once you get to what you want, I would ask you to close this thread down by marking correct and helpful responses so that other people coming to this thread in future can look for answers in the correct and helpful responses.

Qlik Community Tip: Marking Replies as Correct or Helpful

Best,

Sunny