Set expressions for current Quarter and Month sales.

[maahivee]

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.

[sunny_talwar]

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

[maahivee]

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

[sunny_talwar]

‌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?

[maahivee]

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

[sunny_talwar]

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

[maahivee]

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.

Please Help.

[sunny_talwar]

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)

[maahivee]

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.

[sunny_talwar]

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

[maahivee]

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.