topic Re: Set expressions for current Quarter and Month sales. in QlikView

Set expressions for current Quarter and Month sales.

maahivee — Wed, 01 Jul 2015 22:47:08 GMT

Hello friends,

I have a requirement in my project, Where I have to write a set expression to determine current month and current quarter sales

But the problem is that i have tried so many different expressions and is not able to achieve it.

i have tried it in these ways

=num(Sum({$<Month = {'$(=Num(month(today()-1)))'}>} [Extended Price] ) ,'$#,###.') keeping in mind that the date in my DB is in Number format. I don't know where and what am i doing wrong. But this is kind of an urgent requirement for me.. Please help.

Re: Set expressions for current Quarter and Month sales.

sunny_talwar — Wed, 01 Jul 2015 22:50:37 GMT

Try this for this Month (Note you are defining Current Month as Jun(Since today is 1st July and Today's Month - 1 would be Jun:

=Num(Sum({$<Month = {"$(=Num(Month(Today())-1))"}>} [Extended Price] ) ,'$#,###')

Re: Set expressions for current Quarter and Month sales.

sunny_talwar — Wed, 01 Jul 2015 22:53:03 GMT

You probably can improve your code if you have a field which is created in the script as MonthName(Date) as MonthYear

Current Month Price

=Num(Sum({$<MonthYear = {"$(=MonthName(AddMonths(Today(), -1)))"}>} [Extended Price] ) ,'$#,###')

Current Quarter

Not sure how you define your quarter (Jan-Mar is Quarter 1?)

Re: Set expressions for current Quarter and Month sales.

maahivee — Wed, 01 Jul 2015 23:02:01 GMT

Hello Sunny, Thank you very much for the quick reply.

I have tried the 1st expression and is giving 0 where as it should be somewhere around 4 million.

If i want to try the second expression, to make the changes in the script i already have this one in my script

Date (monthstart (TempDate) , 'MMM_YYYY') as MonthYear,

Also yes in my script we have 4 quarters jan-mar as quarter1 and so on.

Re: Set expressions for current Quarter and Month sales.

sunny_talwar — Wed, 01 Jul 2015 23:04:51 GMT

Try this:

=Num(Sum({$<MonthYear = {"$(=Date(AddMonths(Today(), -1), 'MMM_YYYY'))"}>} [Extended Price] ) ,'$#,###')

Actually, the above won't work since it's only a single date. Would you be able to add another MonthYear as

MonthName(TempDate) as MonthYear1?

Re: Set expressions for current Quarter and Month sales.

sunny_talwar — Wed, 01 Jul 2015 23:08:20 GMT

Did that work? Really?

Re: Set expressions for current Quarter and Month sales.

maahivee — Wed, 01 Jul 2015 23:08:24 GMT

Thank you so much sunny. That really worked and to me you are a genius each time you come up solution to every question. Appreciate it very much. what should be used for current quarter sales.

Re: Set expressions for current Quarter and Month sales.

maahivee — Wed, 01 Jul 2015 23:10:31 GMT

It definitely did. I wish i would have been trained by you in qlikview

Re: Set expressions for current Quarter and Month sales.

sunny_talwar — Wed, 01 Jul 2015 23:12:26 GMT

Try this for the Quarter:

=Num(Sum({$<MonthYear = {"$(='>=' & Date(QuarterStart(AddMonths(Today(), -1)), 'MMM_YYYY') & '<=' & Date(QuarterEnd(AddMonths(Today(), -1)), 'MMM_YYYY'))"}>} [Extended Price] ) ,'$#,###')

Make sure this gave you the correct range in a text box when you put this: ='>=' & Date(QuarterStart(AddMonths(Today(), -1)), 'MMM_YYYY') & '<=' & Date(QuarterEnd(AddMonths(Today(), -1)), 'MMM_YYYY') in a text box

HTH

Best,

Sunny

Re: Set expressions for current Quarter and Month sales.

sunny_talwar — Wed, 01 Jul 2015 23:14:10 GMT

Hahahaha I am glad you think that way . I am glad I am able to help.

Sunny

Re: Set expressions for current Quarter and Month sales.

maahivee — Wed, 01 Jul 2015 23:22:22 GMT

Sunny, I am getting a wired number like this $1.708e+007 but when when i put the other expression in a text box it is giving the april-jun.

Re: Set expressions for current Quarter and Month sales.

sunny_talwar — Wed, 01 Jul 2015 23:26:43 GMT

Is this not the output you are expecting? Is there a way for you to check what you are expecting to get as result?

Re: Set expressions for current Quarter and Month sales.

maahivee — Wed, 01 Jul 2015 23:27:23 GMT

Yes i am expecting around 17Mllion as result.

Re: Set expressions for current Quarter and Month sales.

sunny_talwar — Wed, 01 Jul 2015 23:28:22 GMT

So the only issue is the formating?

Re: Set expressions for current Quarter and Month sales.

maahivee — Wed, 01 Jul 2015 23:29:49 GMT

Also since we just entered into Q3 I want to show Q2 sales.

Re: Set expressions for current Quarter and Month sales.

sunny_talwar — Wed, 01 Jul 2015 23:31:49 GMT

Isn't this already showing Q2 Sales (April - June)?

Re: Set expressions for current Quarter and Month sales.

maahivee — Wed, 01 Jul 2015 23:33:40 GMT

I thought it is just showing error since it is giving e.

Re: Set expressions for current Quarter and Month sales.

sunny_talwar — Wed, 01 Jul 2015 23:36:24 GMT

No, I think the formatting needs to be fixed:

=Num(Sum({$<MonthYear = {"$(='>=' & Date(QuarterStart(AddMonths(Today(), -1)), 'MMM_YYYY') & '<=' & Date(QuarterEnd(AddMonths(Today(), -1)), 'MMM_YYYY'))"}>} [Extended Price] ) ,'$#,##0')

Try different formats here

$#,##0 or $#,##0.00 and see if something works.

Best,

Sunny

Re: Set expressions for current Quarter and Month sales.

maahivee — Wed, 01 Jul 2015 23:40:35 GMT

Thank you very much sunny changing the format did work.

Re: Set expressions for current Quarter and Month sales.

sunny_talwar — Wed, 01 Jul 2015 23:45:18 GMT

‌Awesome... I am glad it worked.

Best,

Sunny