Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
Is this what you are looking for?
if(isnull(above(sum(LineSalesAmount)))=-1,1,Sum (LineSalesAmount)/above(Sum (LineSalesAmount)))
you can do this by using set analysis A Primer on Set Analysis
Can you provide a sample file?
yes u can do, and show ur SalesAmount. on y- axis and date as ur x-axis.
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)
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
Is this what you are looking for?
if(isnull(above(sum(LineSalesAmount)))=-1,1,Sum (LineSalesAmount)/above(Sum (LineSalesAmount)))
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.