topic Re: YTD in a Pivot Table, Per Quarter in QlikView

YTD in a Pivot Table, Per Quarter

almalecha — Thu, 02 Jul 2015 14:19:57 GMT

We send out surveys to clients, and I've been asked to create a chart that will show, per survey, the number of responses we received per Fiscal Quarter, and the percent of responses that were 'positive'. Then additionally per Fiscal Quarter, they want to see a Fiscal Year to Date total/percent.

I thought I could try and use Set Analysis to get the cumulative FYTD score per quarter.

I created a variable that returns the minimum date received:

vMinFiscalStartDate = MIN({1} [TB MO Case DT])

Then in my pivot table, the expression to calculate postive responses I'm trying is:

SUM({$<[TB MO Case DT] = {">=$(vMinFiscalStartDate)"} >}[TB MO Number Indicator NBR])

However, that is only returning the number for each quarter, not FYTD. Then I tried the below as the expression (using 1 instead of $), but I'm getting a total of zero for each quarter:

SUM({$<[TB MO Case DT] = {">={1}(vMinFiscalStartDate)"} >}[TB MO Number Indicator NBR])

Does anyone have any suggestions on how to calculate a minimum date outside the chart, and then reference that within a chart?

Thank you,

Re: YTD in a Pivot Table, Per Quarter

Gysbert_Wassenaar — Thu, 02 Jul 2015 15:41:56 GMT

If you put an = character in front of the expression in the variable then the value will be calculated outside the context of the chart. vMinFiscalStartDate: =MIN({1} [TB MO Case DT])

Re: YTD in a Pivot Table, Per Quarter

almalecha — Fri, 03 Jul 2015 19:55:18 GMT

Thank you!

I was able to change the variable, and when putting it in a text box, it remained constant even when I filtered.

My pivot table was still not calculating correctly, so that helped me realize my expression syntax was incorrect, and I ended up using something similiar to:

rangesum(Sum([TB MO Number Indicator NBR]),before(Sum([TB MO Number Indicator NBR]),1,ColumnNo())))

Thank you again for your reply!