Help with set expression

[#1]

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

[Gysbert_Wassenaar]

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.

[maxgro · Accepted solution]

pfa

[#4]

Massimo,

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

[#5]

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)

  )))