6 Replies Latest reply: Dec 2, 2014 2:58 PM by Ama Domevscek

# Variable pivot table

01-01-2014 - 01-11-2014         01-10-2013 01-12-2013

number

52  11                                                              23

51  12                                                              22

50   13                                                             20

49    15                                                            18

Hello I want to do next:

First column 1 weeknrs,

column 2 number sells in period 01-01-2014 - 01-11-2014

column 3 number sells in period 01-10-2013 01-12-2013

The Period is variable.

How can i do this the best?

Create 4 input boxes, from which the user can select a date from a calender?

How can i show variables in the top?

• ###### Re: Variable pivot table

Hi Ama,

can you explain more what do you want to do?

Do you want to load data and you don't know how to structure their?

Or do you have yet your cloud and do you want to realize a dashboard?

Regards

Luca Jonathan Panetta

PLJ Software

• ###### Re: Variable pivot table

I Have a number of sales of cars for every week over the last years.

Now i want people to compare periods of sales.

e.g. period  1/6/2014 - 1/6/2013

against period 31/5/2012 - 31/5/2013

then i must have 2 columns with the period

and rows the different marks of cars giving the number of sales in a pivot tabel

I hope you understand what i mean

• ###### Re: Variable pivot table

Hi Ama,

I now understand what you want and if you use 4 variables for calendars you can do this with set analysis.

You will to convert the variable in a correct date to get the subset of your data in period.

Eg. Sum({<Date={">= \$(=Date(vSecondCalendarDate) <= \$(=Date(vFirstCalendarDate)"}>} Sales)

If you see the help you can find some example but without use of variable.

Regards

Luca Jonathan Panetta

• ###### Re: Variable pivot table

Sum({<DATECOLUMN={">=\$(DateVar1)<=\$(DateVar2)"}>}BRUTO)

DateColumn is column Database

Bruto is column Database

DateVar1 and DateVar2 are two Date variables.

It won't work, what do i wrong?

• ###### Re: Re: Variable pivot table

Hi Ama,

you have to check that the format for field DATECOLUMN is the same of the variables.

Eg. If DATECOLUMN have the format 2014/11/28 you have to check that the variables give you the same format YYYY/MM/DD.

If you look my set analysis in previous post you loose the =

{">= \$(=Date(

Work with date in set analysis is always hard.

I give you two examples that work with dates and set analysis, I hope it help you to solve your problem.

Best regards

Luca Jonathan Panetta

• ###### Re: Re: Variable pivot table

Thx! You're Right. This helped me.