topic Re: Creating a chart that uses different data in the same field. in QlikView

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

Wed, 01 Apr 2015 15:01:33 GMT

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.

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

ramoncova06 — Wed, 01 Apr 2015 16:13:01 GMT

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

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

Wed, 01 Apr 2015 16:16:55 GMT

Can you provide a sample file?

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

buzzy996 — Wed, 01 Apr 2015 16:25:46 GMT

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

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

Wed, 01 Apr 2015 18:09:23 GMT

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)

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

ramoncova06 — Wed, 01 Apr 2015 19:18:25 GMT

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

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

Anonymous — Thu, 02 Apr 2015 07:42:59 GMT

Is this what you are looking for?

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

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

Thu, 02 Apr 2015 13:17:48 GMT

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.