topic Re: PivotTable expressions with date in QlikView

PivotTable expressions with date

alwinsch — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: PivotTable expressions with date

awhitfield — Mon, 12 Dec 2016 10:39:55 GMT

Hi Alwin,

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

Andy

Re: PivotTable expressions with date

ganeshsvm — Mon, 12 Dec 2016 10:44:14 GMT

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

Regards,

GSVM

Re: PivotTable expressions with date

Anonymous — Mon, 12 Dec 2016 10:45:42 GMT

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

Re: PivotTable expressions with date

vinieme12 — Mon, 12 Dec 2016 11:33:18 GMT

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)

Re: PivotTable expressions with date

alwinsch — Mon, 12 Dec 2016 12:29:28 GMT

See attached file for desired output

Re: PivotTable expressions with date

ganeshsvm — Tue, 13 Dec 2016 05:54:30 GMT

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

Re: PivotTable expressions with date

vinieme12 — Tue, 13 Dec 2016 06:13:51 GMT

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

Re: PivotTable expressions with date

alwinsch — Tue, 13 Dec 2016 11:08:43 GMT

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

Re: PivotTable expressions with date

alwinsch — Tue, 13 Dec 2016 11:16:38 GMT

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

Re: PivotTable expressions with date

ganeshsvm — Wed, 14 Dec 2016 07:02:23 GMT

Yes Alwin, the solution wasn't dynamic at all, I was giving you an idea how you get the solution for your requirement.

and hope you have got the solution which you desired.

Regards,

Ganesh

Re: PivotTable expressions with date

ganeshsvm — Wed, 14 Dec 2016 07:24:14 GMT

One question on your query,

Iin the display "<11-2016", do you want to accumulate data for the previous months(from Jan to Oct) or just show sum of previous month(Oct) data?

Re: PivotTable expressions with date

alwinsch — Wed, 14 Dec 2016 12:57:32 GMT

indeed, i want to accumulate everything before < 11-2016 - i called the column "earlier then"

Re: PivotTable expressions with date

ganeshsvm — Wed, 14 Dec 2016 13:50:42 GMT

Fine, That's what I assumed earlier also from your original post where you didn't mention it and also posted my solution accordingly.

Hope you have got your solutions.

Cheers .