How to calculate MTD in Pivot table

[Anonymous]

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

[#2]

[sbaldwin]

Hi, have you tried something like this:

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

Thanks

Steve

[Anonymous]

Hi Ivan,

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

[Anonymous]

Hi Baldwin,

Its not working....:(

[magavi_framsteg]

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.

[#7]

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

[Anonymous]

Hi Byron,

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

[#9]

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))