Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

maahivee
New Contributor III

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.

1 Solution

Accepted Solutions

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?

52 Replies

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

maahivee
New Contributor III

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?

maahivee
New Contributor III

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.

maahivee
New Contributor III

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.

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.

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

Sunny