PivotTable expressions with date

[alwinsch]

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

[awhitfield]

Hi Alwin,

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

Andy

[ganeshsvm]

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

Regards,

GSVM

[Anonymous]

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

[vinieme12]

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)

[alwinsch]

See attached file for desired output

[ganeshsvm]

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]

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!!

[alwinsch]

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]

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