topic Re: Calculate MTD Position using seperate daily values in QlikView

Calculate MTD Position using seperate daily values

Mon, 23 Sep 2013 10:33:14 GMT

Hi,

I am trying to create an expression(s) to calculate MTD values and compare them to the previous month.

My data is based on daily positions and not rolled up positions, i.e. 01 Jan 2013 will have its own value and 02 Jan 2013 will also have its own value and so on.

I have tried a few expressions, but they don't correctly give me what I want to see. For example

For current month

Sum

({<C_Month={'$(=Month(max(C_date)))'}, C_date = {'$(=max(C_date))'} >}Value)

This gives me the value for the latest day only, while I actually want the sum of everything to the latest latest day i.e. 1st of any month to the latest day available. Once I get this I can then create a similar expression and compare it to the same time in the previous month i.e. My overall aim is to compare for example from 01/06/2013 – 15/06/2013 to 01/05/2013 to 15/05/2013

Any help to get this working as intended will be greatly appreciated.

Many thanks

Re: Calculate MTD Position using seperate daily values

jonathandienst — Mon, 23 Sep 2013 11:53:19 GMT

If the expression provided works for the latest day, then I think this should work for MTD for the latest month:

Sum({<C_Month={'$(=Month(max(C_date)))'}, C_date =>}Value)

And this should work for the previous month

Sum({<C_Month={'$(=Month(max(C_date), -1))'}, C_date =>}Value)

Regards

Jonathan

Re: Calculate MTD Position using seperate daily values

Mon, 23 Sep 2013 19:19:21 GMT

Hi Jonathan,

Thanks for your reply.

Your suggestion really only works for the current month. If a user makes a selection and wants to see what happened in July against June, it shows the full month values, rather than the intra month values that I want.

Ideally I would like to see a comparison for 1st June to 15th June and 1st July to 15th July and so on, which will basically be a sum of the values recorded for all those days.

Regards

Re: Calculate MTD Position using seperate daily values

Oleg_Troyansky — Mon, 23 Sep 2013 22:33:26 GMT

If you need to apply the same MTD logic to prior months, then you need to formulate the search condition that will apply separately to month(C_date) and Day(C_Date). Something long the following lines (can't guarantee the exact syntax, just a direction):

Sum({< C_Date={"=(num(C_Month)=num($(=Month(max(C_date)))) and (day(C_Date) <= day(max(C_Date)))"}, C_date =>} Value)

So, in a nutshell, you are selecting dates that have a month that's equal to the highest month and a date that's less or equal to the highest available date. Tweak it to your specific needs.

best,

Oleg Troyansky

www.masterssummit.com

Re: Calculate MTD Position using seperate daily values

Tue, 24 Sep 2013 05:18:22 GMT

Try This,

C_date={'>=$(VstartMonth)<=$(vMaxDate)'}

Create 2 Variable please find below tha values of the variables................

VstartMonth=Date(MonthStart(vMaxDate),'DD/MM/YYYY')

vMaxDate==Date(max(C_date),'DD/MM/YYYY')

Re: Calculate MTD Position using seperate daily values

Tue, 24 Sep 2013 06:14:07 GMT

Hi ,

Find the attached Application which has both MTD & Previous MTD expressions.

Re: Calculate MTD Position using seperate daily values

Tue, 24 Sep 2013 12:13:59 GMT

Have you try this or not,

Try This,

C_date={'>=$(VstartMonth)<=$(vMaxDate)'}

Create 2 Variable please find below tha values of the variables................

VstartMonth=Date(MonthStart(vMaxDate),'DD/MM/YYYY')

vMaxDate==Date(max(C_date),'DD/MM/YYYY')

Re: Calculate MTD Position using seperate daily values

Tue, 24 Sep 2013 13:29:17 GMT

Thanks Bhaskar, that was exactly what I needed.

Regards