6 Replies Latest reply: Oct 7, 2011 9:30 AM by Martin Blanchet-Pedersen

# Logic to show other figures than selected and the dimensions in table

I need to create a Pivot table that shows the Sales and the Sales from last TimePeriod. In the table I have the Time Period as a dimension and I need to be able to select specific time periods in list boxes.

The problem is illustrated like this:

Current Selections:

Period: 22

Week: 01

Pivot Table:

Period & Week; Sales; Sales Last Period

2201; Sum(Sales); Sum(????)

I need to figure out what to put in the formula with the question marks. Further I have attached a simple document to illustrate the problem.

Hope that someone can help me with this issue - and let me know if I should provide more details.

• ###### Re: Logic to show other figures than selected and the dimensions in table

hei

here is start of the solution via script

i use this method a lot for same store on time dimension

hope its helps you

• ###### Logic to show other figures than selected and the dimensions in table

Take a look at John Witherspoon 's sollution in this thread : http://community.qlik.com/thread/24084

That should work for you I think.

Good Luck.

• ###### Re: Logic to show other figures than selected and the dimensions in table

Further i need to be able to compare 2 different values in the expression. E.g. index figures of Sales LY with the field "Year" as a dimension. So i both need to get the sales according to the selection and dimension and the sales according to a different sets of criterias.

• ###### Re: Logic to show other figures than selected and the dimensions in table

The problem is that my script is way more complicated than the example illustrated. I got like 80 million sales transaction allocated to a lot of different dimensions like items, size, color, barcode, transactionid, store, department, departmentgroup, staffid, terminal, time of sales and a lot more.

Further i need to be able to select e.g. the sales from the period minus x combined with the week minus y (and "in real life" i need to be able to perform this "twisted logic" to a lot of different fields). So the needed combinations are "endless" - so i would rather aviod having to define the logics in the script.

• ###### Re: Logic to show other figures than selected and the dimensions in table

Hi Martin,

A simple solution is this expression:

=Above(Sales ,4)

But this only works if ALL your periodes have 4 weeks......

• ###### Re: Logic to show other figures than selected and the dimensions in table

Some of my periods have more than 4 weeks - and sometimes I need to take E.g. Last Year, Same period but one week later.

So what i am looking for is a function to lookup values different from the selections and dimensions in the chart? Is that somehow possible?