topic Re: How to calculate MTD in Pivot table in QlikView

How to calculate MTD in Pivot table

Anonymous — Mon, 22 Aug 2011 06:53:45 GMT

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

Re: How to calculate MTD in Pivot table

Mon, 22 Aug 2011 07:11:54 GMT

How to calculate MTD in Pivot table

sbaldwin — Mon, 22 Aug 2011 07:15:59 GMT

Hi, have you tried something like this:

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

Thanks

Steve

How to calculate MTD in Pivot table

Anonymous — Mon, 22 Aug 2011 07:38:03 GMT

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

Anonymous — Mon, 22 Aug 2011 07:38:45 GMT

Hi Baldwin,

Its not working....:(

Re: How to calculate MTD in Pivot table

magavi_framsteg — Mon, 22 Aug 2011 08:44:47 GMT

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.

Re: How to calculate MTD in Pivot table

Mon, 22 Aug 2011 08:46:30 GMT

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

Anonymous — Mon, 22 Aug 2011 09:55:17 GMT

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

Mon, 22 Aug 2011 10:25:58 GMT

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