Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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
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.
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.
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.
Hi Martin,
A simple solution is this expression:
=Above(Sales ,4)
But this only works if ALL your periodes have 4 weeks......
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?