topic Re: Change in Sales From Week 1 to Week 2 in QlikView

Change in Sales From Week 1 to Week 2

Wed, 05 Jul 2017 17:37:59 GMT

Hi Experts,

In the attached qvw I am able to get the change in sales from prior week compared to latest week by using this expression:

=If(RowNo(TOTAL)=1,0,sum(Amount)-Above(sum(Amount)))

But I am trying to create a list box with the same logic and it's not working.

This is what I am trying and it's not working in list box expression:

=if(sum(Amount)>above(sum(Amount)),'Increase',if(sum(Amount)<above(sum(Amount)),'Decrease'))

Based on the given qvw and the excel file can you help create a list box so that I can easily filter between the increased and decreased values?

stalwar1‌

Re: Change in Sales From Week 1 to Week 2

sunny_talwar — Wed, 05 Jul 2017 17:48:00 GMT

May be like attached

Re: Change in Sales From Week 1 to Week 2

Wed, 05 Jul 2017 18:12:40 GMT

stalwar1‌ Thanks for your quick response but this didn't work as expected. For the qvw you have attached, If you select Decrease it seems like it's working but if you select increase it shows both increase and decrease rows. But when I apply the same logic in the larger data sets both increase and decrease are not filtering correctly. They are showing both the values when either are selected.

Here is the thing,

I have different weeks so the list box for week will have different week start dates in the actual data set and I will always have to be able to filter the data by selecting two weeks at a time. For example the data set I have provided is using 6/5/2017 & 6/26/2017. Likewise I will have a lot of other weeks too.

Can you think of anything that's missing in the expression?

Re: Change in Sales From Week 1 to Week 2

sunny_talwar — Wed, 05 Jul 2017 18:29:35 GMT

Not entirely sure, but try this

Re: Change in Sales From Week 1 to Week 2

Wed, 12 Jul 2017 21:07:41 GMT

Thank you stalwar1‌
This works. I am trying to work with a waterfall chart with the same data. I have added one in the attached qvw. How can I show the values that are decreasing by week below the increasing ones and the vice versa. For example, in the attached qvw weekly amount has decreased in 6/26/2017 compared to 6/5/2017 but in the chart it is showing above 6/5/2017 values. How can I show such decreasing values below and only increasing values above? !

Re: Change in Sales From Week 1 to Week 2

sunny_talwar — Wed, 12 Jul 2017 22:36:46 GMT

Can you elaborate a little for me... You want the second bar to be going below 0? or would it just show the difference? If you want to see the difference, may be like this?

Re: Change in Sales From Week 1 to Week 2

Thu, 13 Jul 2017 15:25:19 GMT

Thanks stalwar1‌

Ok with the logic you have provided if I want to see the difference how can I see the negative values go below 0 and only positive are shown above? So, in the qvw you have provided how to show ($58,000.00) below 0 and if there were values which had increased from time to time they would show above? Can we do that?

Re: Change in Sales From Week 1 to Week 2

sunny_talwar — Thu, 13 Jul 2017 16:39:20 GMT

Remove bar offset

Re: Change in Sales From Week 1 to Week 2

Thu, 13 Jul 2017 17:20:17 GMT

Thank you. This works.