Creating a chart that uses different data in the same field.

[#1]

I have two fields. One is the SalesDate and the other is the SalesAmount. I want to create a Line or Bar chart that shows for each week the sum of SalesAmount divided by the previous week's sum of SalesAmount.

Thanks.

[ramoncova06]

you can do this by using set analysis A Primer on Set Analysis

[#3]

Can you provide a sample file?

[buzzy996]

yes u can do, and show ur SalesAmount. on y- axis and date as ur x-axis.

[#5]

I did try using set analysis was hoping for another solution. Set analysis worked well when I needed things like dashboards. As for charts like line or bar when I use set analysis Qlikview doesn't know what x value to assign to the calculated y value I think.

I have something similar to this right now.

Sum({$<SalesDateID = {"<=$(=Max(SalesDateID))"}, WeekID = {$(=Max(WeekID))}>} SalesAmount)

/

Sum({$<SalesDateID = {"<=$(=Max(SalesDateID)-7)"}, WeekID = {$(=Max(WeekID)-1)>} SalesAmount)

[ramoncova06]

your set analysis wouldn't work because you are limiting the week that are being considered, I am trying to think on how to do this on with set analysis

Though this could be done at the script level by summing all sales and then left joining them back into you fact table

[Anonymous · Accepted solution]

Is this what you are looking for?

if(isnull(above(sum(LineSalesAmount)))=-1,1,Sum (LineSalesAmount)/above(Sum (LineSalesAmount)))

[#8]

This is exactly what I was looking for. Just curious do you happen to know what is the difference between TOP and ABOVE? It seems like it is very small.