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 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] ) ,'$#,###')

[sunny_talwar]

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

[maahivee]

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.

[sunny_talwar · Accepted solution]

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?

[sunny_talwar]

Did that work? Really?

[maahivee]

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.

[maahivee]

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

[sunny_talwar]

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

[sunny_talwar]

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

Sunny