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: 
Not applicable

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?

Please advice to realize.

1 Solution

Accepted Solutions
pljsoftware
Creator III
Creator III

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

View solution in original post

6 Replies
pljsoftware
Creator III
Creator III

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

Not applicable
Author

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

thx in advance

pljsoftware
Creator III
Creator III

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.

I hope this help you.

Regards

Luca Jonathan Panetta

Not applicable
Author

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?

pljsoftware
Creator III
Creator III

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

Not applicable
Author

Thx! You're Right. This helped me.