Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
martinpedersen
Creator
Creator

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.

6 Replies
lironbaram
Partner - Master III
Partner - Master III

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

Anonymous
Not applicable

I would suggest that you adjust your script.

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.

martinpedersen
Creator
Creator
Author

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.

martinpedersen
Creator
Creator
Author

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.

Anonymous
Not applicable

Hi Martin,

A simple solution is this expression:

        =Above(Sales ,4)

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

martinpedersen
Creator
Creator
Author

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?