8 Replies Latest reply: Aug 22, 2011 6:25 AM by Byron Van Wyk

# How to calculate MTD in Pivot table

Hi All,

M adding an excel sheet pls let me know how to calculate MTD in pivot table on Expression Level.

Pls see the row marked with red in Excel

Thanks,

Shashank

• ###### How to calculate MTD in Pivot table

Hi Ivan,

Im using Qlikview 9 there is no such option in it....Pls correct if I am wrong

• ###### How to calculate MTD in Pivot table

Hi, have you tried something like this:

=rangesum(above(yourfieldname,1,rowno()))

Thanks

Steve

• ###### How to calculate MTD in Pivot table

Hi Baldwin,

Its not working....:(

• ###### Re: How to calculate MTD in Pivot table

Hi Shashank,

Firstly I want to change your excel spreadsheet, by loading the data using using crosstable for the dates. Also I'm not sure about why you want to show dates running across columns with the weeks underneath. All together, for me, the table doesnt make much sense. I have managed to achieve what you are looking for. Totals are on top, but this can be changed in the sort option.

Most of the work has been in the script. I also used the total function in the expression to get the MTD Total to work. Hope this is what you are were looking for.

I've attached 3 objects. Your QV report using QV 9, A revised excel doc and an IMPORTANT image. Make sure this box is unticked

• ###### Re: How to calculate MTD in Pivot table

Hi Byron,

I send u the format excel but actually I have to take data from QVD.....so crosstable won't work.....Pls Suggest

• ###### Re: How to calculate MTD in Pivot table

In that case we use a few ideas from the previously loaded documents

1) Create and INLINE load where we CONCATENATE. This is so we have MTD Total appearing as a field name. Can't think of another way to do this
2) Use the expression if(WeekNo='MTD Total' or WeekEndDate='MTD Total',sum(TOTAL<Date>Value),sum(Value))

• ###### Re: How to calculate MTD in Pivot table

Hi.

I think the best way is to connect a calendar to your fact table in your script.

In the calendar you then create fields for YTD, MTD, ThisMonth, LastMonth etc.

Then in your expression, you can get the MTD by using a {set}, something like (pseudo code):

sum({\$ MTD = {1}} Revenue)

Hope this helps.

You can find good calendar scripts in the community.