topic Re: Pivot Table with YTD and detailed data in QlikView

Pivot Table with YTD and detailed data

Wed, 13 Jun 2012 13:56:01 GMT

I have a pivot table with a YTD column that I need to calculate and detailed data by month. I have tried setting an expression for the YTD column using set analysis with Sum, but when I add this then the dimension for the field MON does not work correctly in the pivot table. I have attached the qvw where I have the YTD expression as a simple sum, so that you can see the data. Below is what I have tried to set the expression to, but once I add this the MON dimension no longer works when I change it to "March".

sum( {1<MON = {'April', 'March'}>} [BU Volume] )

Re: Pivot Table with YTD and detailed data

MayilVahanan — Wed, 13 Jun 2012 14:54:10 GMT

Hi,

Try this,

=Sum({$<Mon={'April','March'}>}[BU Volume])

Hope it helps

Re: Pivot Table with YTD and detailed data

Wed, 13 Jun 2012 19:18:22 GMT

No, it does not work, it does not add the 2 dollar amounts for April and March for a YTD.

What I am trying to do is if April is $5 and March is $3 then YTD would be $8. I need to clear the dimension MON in order to add the two values for March and April, I want the YTD to be $8 when I select a MON of April or March.

Re: Pivot Table with YTD and detailed data

CELAMBARASAN — Wed, 13 Jun 2012 19:50:35 GMT

Hi,

Your YTD expression should be

Sum({<FY=,MON=,FM={">=$(=YearStart(Max(FM)))<=$(=Max(FM))"}>} [BU Volume] )

Celambarasan

Re: Pivot Table with YTD and detailed data

Wed, 13 Jun 2012 20:19:39 GMT

This is not producing YTD numbers. It still gives the value for the current month. For example, if April is $5 and March is $3 then it is giving the YTD as $5 for April and $3 for March, it should be $8 for March and $8 for April.

Re: Pivot Table with YTD and detailed data

CELAMBARASAN — Wed, 13 Jun 2012 20:25:39 GMT

Hi,

Sum(TOTAL <FY>{<FY=,MON=,FM={">=$(=YearStart(Max(FM)))<=$(=Max(FM))"}>} [BU Volume] )

Re: Pivot Table with YTD and detailed data

Wed, 13 Jun 2012 20:30:54 GMT

This gives me YTD for April, but not March.

Re: Pivot Table with YTD and detailed data

MayilVahanan — Wed, 13 Jun 2012 20:33:32 GMT

Hi,

Try this,

=Sum(TOTAL{<MON={'April','March'}>}[BU Volume])

Hope it helps

Re: Pivot Table with YTD and detailed data

Thu, 14 Jun 2012 00:36:36 GMT

That worked, but the real data file does not have the MON field, it takes the FM field to determine the month by =Month(FM). So, in the dimension is “=Month(FM)” instead of MON, I have tried replacing “MON” with “Month(FM)” but that is invalid, any ideas?

=Sum(TOTAL{<=Month(FM)={'April','March'}>}[BU Volume])