如何显示与集合表达式的比较？(How to display comparisons with set expression?)

如何显示与集合表达式的比较？(How to display comparisons with set expression?)

我的数据集有WeekEndingDate和Sales。 我正在显示一个包含所有选定数据的直表，但我需要另一个表显示以下内容：

Sales (other columns...) First week : 1,000 Last week : 1,350 Difference : 350 Difference %: 35%

我的问题：

a）我可以在一个图表/表中使用上述内容，还是需要4个不同的图表来显示按集合表达式过滤的列？

b）我的策略是有2个变量（vMinWeek和vMaxWeek），并在set表达式中使用它们。 这是最好的路线吗？

c）我的集合表达式（如下）不起作用 - 它们对整个数据集求和。 你能帮我理解为什么吗？

=max ({$<WeekEndingDate={'$(vMinWeek)'}>} Sales)

感谢您的帮助！

马拉

My dataset has WeekEndingDate and Sales. I am displaying a straight table with all the selected data but I need to have another table showing the following:

Sales (other columns...) First week : 1,000 Last week : 1,350 Difference : 350 Difference %: 35%

My questions:

a) Can I have the above in one chart/table, or I need 4 different charts showing columns filtered by set expressions?

b) My strategy is having 2 variables (vMinWeek and vMaxWeek), and using them in set expressions. Is that the best route?

c) My set expressions (below) are not working - they sum the whole data set. Would you please help me understanding why?

=max ({$<WeekEndingDate={'$(vMinWeek)'}>} Sales)

Thank you for your help!

Mara

最满意答案

我认为你的设置不起作用的原因是你的WeekEnd日期被格式化为日期，你的变量被格式化为数字。

Set Analysis的技巧总是要考虑你需要在列表框中输入什么来得到你的答案。 因此，即使QlikView将WeekEnd 2014/08/18存储为41869，您也无法在WeekEnd列表框中键入41869并返回该日期。 所以我会把你的变量=date(min(WeekEnd)) form =date(min(WeekEnd)) 。

问题的第二部分; 得到你想要的桌子。 我会这样做的。 我使用维度值制作一个松散的表，对应的是它在我们要构建的图表中正确排序。

load dual(D,N) as DIM inline [ D,N First Week,1 Last Week,2 Difference,3 Dif %,4 ];

我喜欢在脚本中定义我的变量，所以我会这样做。

set vFirstWeek='=date(min(WeekEnd))'; set vLastWeek='=date(max(WeekEnd))';`

然后在构建直表时我们使用DIM作为DIM，但由于DIM没有连接到任何东西，我们必须做一些工作才能让它显示符合这些尺寸值的值。 num(,'# ##0')只是将％与sums格式化。 为此，必须将图表的“数字”选项卡中的数字格式设置为“表达式默认值”。

if(DIM='First Week',num(sum({<WeekEnd={'$(vFirstWeek)'}>} Sales),'# ##0'), if(DIM='Last Week',num(sum({<WeekEnd={'$(vLastWeek)'}>} Sales),'# ##0'), if(DIM='Difference',num(sum({<WeekEnd={'$(vFirstWeek)'}>} Sales)-sum({<WeekEnd={'$(vLastWeek)'}>} Sales),'# ##0'), if(DIM='Dif %',num((sum({<WeekEnd={'$(vFirstWeek)'}>} Sales)-sum({<WeekEnd={'$(vLastWeek)'}>} Sales))/sum({<WeekEnd={'$(vLastWeek)'}>} Sales),'0.00%')))))

I think the reason your set isn't working is that your WeekEnd date is formatted as a date and your variable is formatted as a number.

The trick with Set Analysis is always to think what you would have to type in a list box to get to your answer. So even though QlikView stores WeekEnd 2014/08/18 as 41869 you can't type 41869 in the WeekEnd list box and get back that date. So I would make your variables of the form =date(min(WeekEnd)).

The second part of your question; getting the table you want. I would do like this. I make a loose table with the dimension values, dual is so that it sorts correctly in the chart we are going to build.

load dual(D,N) as DIM inline [ D,N First Week,1 Last Week,2 Difference,3 Dif %,4 ];

I like defining my variables in the script as well, so I would do this.

set vFirstWeek='=date(min(WeekEnd))'; set vLastWeek='=date(max(WeekEnd))';`

Then when building the straight table we use the dimension as DIM but because DIM isn't connected to anything we have to do some work to get it to display values that fit those dimension values. The num(,'# ##0') is just to format the % differently from the sums. For this to work the number format in the Number tab of the chart must be set to Expression Default.

if(DIM='First Week',num(sum({<WeekEnd={'$(vFirstWeek)'}>} Sales),'# ##0'), if(DIM='Last Week',num(sum({<WeekEnd={'$(vLastWeek)'}>} Sales),'# ##0'), if(DIM='Difference',num(sum({<WeekEnd={'$(vFirstWeek)'}>} Sales)-sum({<WeekEnd={'$(vLastWeek)'}>} Sales),'# ##0'), if(DIM='Dif %',num((sum({<WeekEnd={'$(vFirstWeek)'}>} Sales)-sum({<WeekEnd={'$(vLastWeek)'}>} Sales))/sum({<WeekEnd={'$(vLastWeek)'}>} Sales),'0.00%')))))