
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
Pls reply ASAP
Thanks,
Shashank

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi, have you tried something like this:
=rangesum(above(yourfieldname,1,rowno()))
Thanks
Steve

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi Ivan,
Im using Qlikview 9 there is no such option in it....Pls correct if I am wrong

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi Baldwin,
Its not working....:(


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi Byron,
I send u the format excel but actually I have to take data from QVD.....so crosstable won't work.....Pls Suggest

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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))
