topic Re: Calculating Month to Date Sales Value in QlikView

Calculating Month to Date Sales Value

Thu, 12 Dec 2013 09:14:40 GMT

Dear All,

We are in FMGC Company and we are receiving sales data from distributor in daily basis.

Now i need to calculate MTD sales, when i select 12th December i need 12 days sum or when i select 5th Dec 5 i need 5 days sales.

I have applied this formula but it's not giving correct value.

=Sum({$<Month = {$(=Max(Month))},

YearFiscal = ,

Quarter = ,

Month = >} ValuesinMT)

Highly appreciated if any one could help me.

Thanks in advance

Raju

Re: Calculating Month to Date Sales Value

Thu, 12 Dec 2013 09:24:09 GMT

Hi Raju

Is Month your selected field or do you need to select Day ?

=Sum({$<Day= {=$(=Max(Day))},

YearFiscal = ,

Quarter = ,

Month = } ValuesinMT)

chris

Re: Calculating Month to Date Sales Value

Thu, 12 Dec 2013 09:33:32 GMT

Try this

sum({<Date_field= {"<=$(=Date_Field)>=$(=MonthStart(Date_Field))"}

YearFiscal = ,

Quarter = ,

Month =

> } ValuesinMT)

Regards,

Kabilan K

Re: Calculating Month to Date Sales Value

Thu, 12 Dec 2013 10:09:15 GMT

Thank you so much christian....

I have applied this formula but it's not giving correct value. say for example if select Dec 5, it's giving only 5th day sales in all month.

Rgds,

Raju

Re: Calculating Month to Date Sales Value

Thu, 12 Dec 2013 10:18:40 GMT

Thank you so much kabilan...

I have tried the way what you are suggested. It's giving only current date sales.

Is there any other way to move forward?

Rgds.

Raju

Re: Calculating Month to Date Sales Value

Fri, 13 Dec 2013 05:30:05 GMT

Try this

sum({1<Date_field= {"<=$(=Date_Field)>=$(=MonthStart(Date_Field))"}

YearFiscal = ,

Quarter = ,

Month =

> } ValuesinMT)

Re: Calculating Month to Date Sales Value

tresB — Fri, 13 Dec 2013 05:31:29 GMT

Could you please post your sample app?

Re: Calculating Month to Date Sales Value

jagan — Fri, 13 Dec 2013 05:47:32 GMT

Hi Raju,

Try like this if you have a date field

=Sum({$<Month =, YearFiscal = , Quarter = , DateDimension={'>=$(=MonthStart(Max(DateDimension)))<=$(=Date(Max(DateDimension)))'} >} ValuesinMT)

Note : If you have any other date related then ignore selection by give DimensionName=.

If you are not getting values check the date format in DateDimension and MonthStart(Max(DateDimension)) are same if not format the date by using Date().

Hope this helps you.

Regards,

Jagan.

Re: Calculating Month to Date Sales Value

Fri, 13 Dec 2013 10:54:42 GMT

Thank you so much kabilan, It working perfectly .....

Great Help. Much appreciated.

Thanks & Regards,

Raju

Re: Calculating Month to Date Sales Value

Fri, 13 Dec 2013 10:56:06 GMT

Thank you so much Jagan.

Your coding is working perfectly. Great Help, Much appreciated.

Thanks & Regards,

Raju