topic Re: Help with set expression in QlikView

Help with set expression

Fri, 20 Nov 2020 18:02:30 GMT

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

Re: Help with set expression

Gysbert_Wassenaar — Mon, 18 May 2015 17:21:42 GMT

a) One chart is probably possible, but four charts might be easier.

b) Probably. Without looking at your data it's hard to be certain.

c) No idea and no way to tell without looking at the qlikview document.

Re: Help with set expression

maxgro — Mon, 18 May 2015 18:16:55 GMT

pfa

Re: Help with set expression

Tue, 19 May 2015 09:49:34 GMT

Massimo,

Thank you so much for your answer. I had no idea about ValueList.

Re: Help with set expression

Tue, 19 May 2015 09:52:13 GMT

Just to summarize Massimo's answer:

1) Use ValueList('First Week', 'Last Week', 'Difference') as a dimension

2) Expressions use "if":

= if(ValueList('First Week', 'Last Week', 'Difference')='First Week', sum ({$<WeekEndingDate={'$(vMinWeek)'}>} Sales),

if(ValueList('First Week', 'Last Week', 'Difference')='Last Week', sum ({$<WeekEndingDate={'$(vMaxWeek)'}>} Sales),

if(ValueList('First Week', 'Last Week', 'Difference')='Difference',

sum ({$<WeekEndingDate={'$(vMaxWeek)'}>} Sales) - sum ({$<WeekEndingDate={'$(vMinWeek)'}>} Sales)

)))