Skip to main content
Announcements
Marching toward a simplified navigation! READ ON
cancel
Showing results for 
Search instead for 
Did you mean: 
alwinsch
Creator
Creator

PivotTable expressions with date

Hi all,

I want to create a Pivot Table, with expressions like <11/2016 ---- 11/2016 --- and 12/2016 as column headers

how can i create this, when i have the following fields:

paydate ( dd/mm/YYYY),

Amount

so i want the exact amount in the correct column when the date is correct.

Can the column headers be dynamic? when i call this report next month, the headers should look like the following:

<12/2016 --- 12/2016 --- 01/2017

thanks for your help.

Alwin

13 Replies
awhitfield
Partner - Champion
Partner - Champion

Hi Alwin,

could you upload an example of the data that you are using?

Andy

ganeshsvm
Creator II
Creator II

upload an image that's shows your requirement and sample data.

Regards,

GSVM

Anonymous
Not applicable

Yes, in the label field, you can enter an expression.

rotulo.JPG

vinieme12
Champion III
Champion III

Drag the PayDate field to the column

and your expression should restrict the dates you want to see the data for

Example create two variables having from and to dates

vStartDate: date#('01/11/2016','DD/MM/YYYY')

vEndDate: date#('31/12/2016','DD/MM/YYYY')

The expression should be as below:

SUM({<PayDate={'>=$(vStartDate)<=$(vEndDate)'}>} Amount)

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.
alwinsch
Creator
Creator
Author

See attached file for desired output

Example.png

ganeshsvm
Creator II
Creator II

Hi,

First you need to create bucket of your desired date range, like below,

LOAD if(Month(PayDate)<11,'<11-2016',num(Month(PayDate))&'-'&Year(PayDate)) as PayDateBucket, * Inline [
ID, Name, PayDate, Amount
1,A,25-10-2016,400
2,A,25-11-2016,200
3,B,21-12-2016,100
4,B,12-12-2016,300
5,C,21-11-2016,700
6,C,06-09-2016,100
]
;

Then you can use sum(Amount)  in pivot table as expression and add Name and PayDateBucket  as dimensions, the just drag the PayDateBucket column to the top and you will get something like this,

Hope this helps,

Regards,

GSVM

vinieme12
Champion III
Champion III

Example create two variables having from and to dates

vStartDate: date#('11/12/2016','DD/MM/YYYY')

vEndDate: date#('12/12/2016','DD/MM/YYYY')

Add Calculated Dimension

=if(PayDate <  $(vStartDate) , '<' & $(vStartDate)

,if(PayDate>=$(vStartDate) OR PayDate<=$(vEndDate) , PayDate))

The expression should be as below:

SUM(Amount)

If you need more assistance post a sample in Excel NOT IMAGE, you cannot load tables from Images!!

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.
alwinsch
Creator
Creator
Author

But then nothing is dynamic, next month my dimensions will vary  from <11-2016 to <12-2016

in this case i will have to edit script every month?

regards,

Alwin

alwinsch
Creator
Creator
Author

In the meanwhile i figured it out myself

Created 3 variables and used them in the expression within the if statement.

variables

Date(MonthStart(Paydate), 'MM-YYYY') as MJ

Date(Monthstart(Today()),'MM-YYYY') as thismonth

Date(AddMonths(Monthstart(Today()), -1), 'MM-YYYY') as PreviousMonth

variables:

= if(($(MJ) = $(PreviousMonth)) , sum(Bedrag),0)

regards,

Alwin