52 Replies Latest reply: Aug 27, 2015 11:19 AM by mahathi vucha

# Set expressions for current Quarter and Month sales.

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.

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.

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.

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.

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.

Did that work? Really?

• ###### Re: Set expressions for current Quarter and Month sales.

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

• ###### Re: Set expressions for current Quarter and Month sales.

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.

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.

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, 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.

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.

Yes i am expecting around 17Mllion as result.

• ###### Re: Set expressions for current Quarter and Month sales.

So the only issue is the formating?

• ###### Re: Set expressions for current Quarter and Month sales.

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

• ###### Re: Set expressions for current Quarter and Month sales.

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

• ###### Re: Set expressions for current Quarter and Month sales.

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

• ###### Re: Set expressions for current Quarter and Month sales.

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.

Thank you very much sunny changing the format did work.

• ###### Re: Set expressions for current Quarter and Month sales.

Awesome... I am glad it worked.

Best,

Sunny

• ###### Re: Set expressions for current Quarter and Month sales.

Hello Sunny- The problem is that for those expressions they are still showing last month and last quarter sales. No matter what. I dont know where it went wrong suddenly. Can you please help me one again.

• ###### Re: Set expressions for current Quarter and Month sales.

Sure, but you will need to elaborate on what is happening.

• ###### Re: Set expressions for current Quarter and Month sales.

Sunny- The problem is that,

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

In this expression, we are using addmonths  function and since we have to give a parameter for addmonths function and that parameter here is -1, so it is adding up last month and last quater sales for both MTD and QTD. So i changed the parameter to be 0 now and it is giving the correct numbers, But the problem is that now i am not able to define the MTD sales for last year Say like sales from july 1st to july 7th of 2014.

Usually i do like this to determine last year sales

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

But in this case it is only giving last month sales. I know i am a bit sloppy here but if you can understand what i am trying to say, it is really nice.

• ###### Re: Set expressions for current Quarter and Month sales.

In short i am trying to say that i am not able to select max(year)-1 kind of filter in this expression.

• ###### Re: Set expressions for current Quarter and Month sales.

if you give me the date ranges you want. I will make it to work.

lets assume the max date in your data is 06/30/2015. what ranges should your expression cover? give me different date ranges for different expressions.

• ###### Re: Set expressions for current Quarter and Month sales.

i am just trying to do like simple MTD expressions which is like starting from the 1st of the current month which is July now to the current date which is 8th but since we dont have the data for 8th, i want to show 1st jul to 7th jul of 2015 which i did using the expression

Sum({\$<MonthYear = {"\$(=Date(AddMonths(Today(), 0), 'MMM_YYYY'))"}>} [Extended Price] )

But now i want to do the same thing for the years 2014 and 2013.

• ###### Re: Set expressions for current Quarter and Month sales.

Got it. Will send you what you need in a bit.

Best,

Sunny

• ###### Re: Set expressions for current Quarter and Month sales.

For 2015: 07/01/2015 - 07/08/2015

=Sum({\$<MonthYear = {"\$(='>=' & Date(MonthStart(Today()), 'MMM_YYYY') & '<=' & Date(Today(), 'MMM_YYYY'))"}>} [Extended Price])

For 2014:07/01/2014 - 07/08/2014

=Sum({\$<MonthYear = {"\$(='>=' & Date(MonthStart(AddYears(Today(), -1)), 'MMM_YYYY') & '<=' & Date(AddYears(Today(), -1), 'MMM_YYYY'))"}>} [Extended Price])

For 2013:07/01/2013 - 07/08/2013

=Sum({\$<MonthYear = {"\$(='>=' & Date(MonthStart(AddYears(Today(), -2)), 'MMM_YYYY') & '<=' & Date(AddYears(Today(), -2), 'MMM_YYYY'))"}>} [Extended Price])

I hope this is what you want?

Best,

Sunny

• ###### Re: Set expressions for current Quarter and Month sales.

Do you want these ranges if Max is 06/30/2015

1) 06/01/2015 - 06/30/2015

2) 06/01/2014 - 06/30/2014

3) 04/01/2015 - 06/30/2015

4) 04/01/2014 - 06/30/2014

All of the above?

• ###### Re: Set expressions for current Quarter and Month sales.

yes but just 1 and 2, not 3 and 4.

• ###### Re: Set expressions for current Quarter and Month sales.

Try these:

For 2015: 07/01/2015 - 07/08/2015

=Sum({\$<MonthYear = {"\$(='>=' & Date(MonthStart(Today()), 'MMM_YYYY') & '<=' & Date(Today(), 'MMM_YYYY'))"}>} [Extended Price])

For 2014:07/01/2014 - 07/08/2014

=Sum({\$<MonthYear = {"\$(='>=' & Date(MonthStart(AddYears(Today(), -1)), 'MMM_YYYY') & '<=' & Date(AddYears(Today(), -1), 'MMM_YYYY'))"}>} [Extended Price])

For 2013:07/01/2013 - 07/08/2013

=Sum({\$<MonthYear = {"\$(='>=' & Date(MonthStart(AddYears(Today(), -2)), 'MMM_YYYY') & '<=' & Date(AddYears(Today(), -2), 'MMM_YYYY'))"}>} [Extended Price])

I hope this is what you want?

Best,

Sunny

• ###### Re: Set expressions for current Quarter and Month sales.

Sunny- i am Sorry to bother you like this, But it is just showing July 2014 whole month not till the max date.

• ###### Re: Set expressions for current Quarter and Month sales.

i thought you wanted an expression that gives you 07/01/2014 to 07/08/2014? No you want it to go all the way to 07/08/2015?

• ###### Re: Set expressions for current Quarter and Month sales.

No you are right i want it till 7/1/2015 to 7/8/2015.

• ###### Re: Set expressions for current Quarter and Month sales.

For 7/1/2015 to 7/8/2015 use this:

=Sum({\$<MonthYear = {"\$(='>=' & Date(MonthStart(Today()), 'MMM_YYYY') & '<=' & Date(Today(), 'MMM_YYYY'))"}>} [Extended Price])

For 7/1/2014 to 7/8/2014 use this:

=Sum({\$<MonthYear = {"\$(='>=' & Date(MonthStart(AddYears(Today(), -1)), 'MMM_YYYY') & '<=' & Date(AddYears(Today(), -1), 'MMM_YYYY'))"}>} [Extended Price])

• ###### Re: Set expressions for current Quarter and Month sales.

Hello, Now i have one more tricky req in which i have to find out which PO comes 1st meaning that:

say this particular item 123 has 5 PO's in order but I have to find out which one is coming 1st based on the PO Date and find out the QTY for that particular PO and put in a column.

But Can you please help me with writing this expression, I have already tried different forms of it like max(PO date) and min(PO date), But nothing seems to be working.

• ###### Re: Set expressions for current Quarter and Month sales.

Try using FirstSortedValue() function:

for Max(Date) -> FirstSortedValue(QTY, -Aggr([PO date], Item))

for MIN(Date) -> FirstSortedValue(QTY, Aggr([PO date], Item))

Not entirely sure if they can be used as it is... but this is how you can find a value for max and min dates

FirstSortedValue(ValueOnMaxDate, -Date)

• ###### Re: Set expressions for current Quarter and Month sales.

Hello Sunny- I dont need to show Min date but only max date, Also i want to show the sum(qty) for that item for 1st Coming PO in a column. will this still work.

• ###### Re: Set expressions for current Quarter and Month sales.

Do you have a sample you can share? just a few rows of data should suffice

• ###### Re: Set expressions for current Quarter and Month sales.

Sunny - Please the attached excel it has sample data of qty, item and PO date.

So the thing is that in a new Column have to show the Qty for 1st PO in  order. based on that date.

• ###### Re: Set expressions for current Quarter and Month sales.

This is what you are looking for?

• ###### Re: Set expressions for current Quarter and Month sales.

Yes- The same thing.

• ###### Re: Set expressions for current Quarter and Month sales.

I am attached the qvw file for you to check out (Re: Set expressions for current Quarter and Month sales.)

HTH

Best,

Sunny

• ###### Re: Set expressions for current Quarter and Month sales.

Excellent, This is what i have been looking for.. Thank you very much.

Also wanted to tell you one more thing, i was actually following you in this community inbox, everyday i check my email it is loaded with your mails which means the response you are giving to people out there in need of help.

which make me think you are such an awesome person spending most of your time helping out others, to me you are awesome person and genius mind.

I am so impressed and overwhelmed, thank you so much  for all.

• ###### Re: Set expressions for current Quarter and Month sales.

Thanks my friend for thinking that I am a genius, but I do have a self interest. The more I help, the more I learn. So I am no genius, just another guy like you .

Best,

Sunny

• ###### Re: Set expressions for current Quarter and Month sales.

Thats really sweet of you to say like that. Thank you very much. Any time i have a prob or struck some where, i know whom to reach for.

• ###### Re: Set expressions for current Quarter and Month sales.

Hello Sunny- Actually I am sorry but i blindly added the same expression for my report and i got picked coz it is actually showing the value based on the meaning that say for example lets take item# 639 it has dates from 6-04 to 7-08 but the thing is it giving the 1st PO based on the sort if it asce or desc order.

But my requirement should actually show the 1st PO in order no matter what For ex for item 639 it should always show the Po 6-04.

• ###### Re: Set expressions for current Quarter and Month sales.

You will have to recap me man. I have no idea what you are looking for

• ###### Re: Set expressions for current Quarter and Month sales.

Sunny- If you go to the QVW you attached to this tread you may recall it.

basically i am looking for 1st PO in Order for each item based on the PO date.

• ###### Re: Set expressions for current Quarter and Month sales.

Checking it now

• ###### Re: Set expressions for current Quarter and Month sales.

I am looking at it now. Do you want to see this First value for date and first value of quantity or you want sum of quantity on first date for an item?

• ###### Re: Set expressions for current Quarter and Month sales.

I want to see the sum(qty) for the 1st date.

• ###### Re: Set expressions for current Quarter and Month sales.

Hello Everyone- In the same requirement i want to find out the 1st po date and 1st po Qty

to get the 1st po date i am doing min(po date) which is working fine

but to get the 1st po qty i am trying to do

=Alt(FirstSortedValue(Aggr(Sum([Qty On Order]), [Item Number], [Promise Date]), [Promise Date]), Aggr(Sum([Qty On Order]), [Item Number], [Promise Date]))

=Sum({<[Promise Date] = {"\$(=Min([Promise Date]))"}>} [Qty On Order])

Both the expressions are not working for me please give me some suggetions how to get the 1st PO Qty for that item.

the other thing happening =Sum({<[Promise Date] = {"\$(=Min([Promise Date]))"}>} [Qty On Order]) with this expression is that it is working only if i select one item, and is displaying all over when no item is selected in the chart.