topic Re: Calculated Date Dimensions in a Pivot Table? in QlikView

Calculated Date Dimensions in a Pivot Table?

aritting — Wed, 17 Jun 2015 19:46:54 GMT

I would like to create a pivot table that has the current Year and the current and previous Quarter as calculated dimensions.

I've found that I can get current year by year(today()) expression butI'm not sure how to get the current and previous quarter.

Re: Calculated Date Dimensions in a Pivot Table?

sunny_talwar — Wed, 17 Jun 2015 19:55:01 GMT

May be like this:

If(Match(Year, Year(Today(), Year(Today()-1), Year) for Year Dimension

If((Date >= QuarterStart(Today()) and Date <=QuarterEnd(Today())) or (Date >= QuarterStart(AddYears(Today(), -1)) and Date <=QuarterEnd(AddYears(Today(), -1))), Quarter) for Quarter Dimesnion

Check the option on the Dimensions tab - > Suppress When Value Is Null for both the dimensions.

HTH

Best,

Sunny

Re: Calculated Date Dimensions in a Pivot Table?

aritting — Wed, 17 Jun 2015 20:13:04 GMT

I'm not following what is going on there.

Re: Calculated Date Dimensions in a Pivot Table?

aritting — Wed, 17 Jun 2015 20:21:13 GMT

I want to see a pivot table chart where the current year is the calculated dimension and the current and previous quarter

2015 Q1

Re: Calculated Date Dimensions in a Pivot Table?

sunny_talwar — Wed, 17 Jun 2015 20:24:45 GMT

I thought you needed 2015 Q2 and 2014 Q2. For what you need, try this:

Two of your dimensions will be

If(Match(Year, Year(Today(), Year) for Year Dimension

If((Date >= AddMonths(QuarterStart(Today()), -3) and Date <=QuarterEnd(Today())), Quarter) for Quarter Dimesnion

Re: Calculated Date Dimensions in a Pivot Table?

aritting — Wed, 17 Jun 2015 20:52:16 GMT

=if(Match(Year, Year(Today())),Year)

Re: Calculated Date Dimensions in a Pivot Table?

MarcoWedel — Wed, 17 Jun 2015 21:29:43 GMT

Hi,

another solution could be:

hope this helps

regards

Marco