topic Re: Need Help to write the expression. in QlikView

Need Help to write the expression.

suvechha_b — Wed, 09 Apr 2014 07:00:04 GMT

How can I write the expression :

SUM({<[Forecast Fiscal Month] = {'$(=([Forecast Fiscal Month],-1))'}>}[Stag Forecast Units])

Re: Need Help to write the expression.

MK_QSL — Wed, 09 Apr 2014 07:02:28 GMT

SUM({<[Forecast Fiscal Month] = {"$(=Max([Forecast Fiscal Month])-1)"}>}[Stag Forecast Units])

Re: Need Help to write the expression.

tresB — Wed, 09 Apr 2014 07:04:07 GMT

It depends on how the data look like in your field [Forecast Fiscal Month], like 'Jan', 'Feb' ..., or 1,2,...? If like 'Jan', ..then,is it created using month() in the script or simply a string represenattion?

Re: Need Help to write the expression.

its_anandrjs — Wed, 09 Apr 2014 07:04:19 GMT

Write like

=SUM({<[Forecast Fiscal Month] = {$(=Max([Forecast Fiscal Month])-1)}>} [Stag Forecast Units])

Re: Need Help to write the expression.

Wed, 09 Apr 2014 07:05:24 GMT

you can use

SUM({<[Forecast Fiscal Month] = {'$(=(Month(Addmonths(Date,-1)))'}>}[Stag Forecast Units]).

for subtracting a month from the date field

himank

Re: Re: Need Help to write the expression.

suvechha_b — Wed, 09 Apr 2014 07:11:00 GMT

The data has to calculated through the period

Jul 2013 Aug 2013 Sep 2013 .......................Jun 2014

Sample : =SUM({<[fis_month] = {$(=Max([Forecast Fiscal Month])-1)}>} [Stag Forecast Units])

is not working...

Re: Re: Need Help to write the expression.

suvechha_b — Wed, 09 Apr 2014 07:13:44 GMT

The data has to calculated through the period

Jul 2013 Aug 2013 Sep 2013 ....................... Jun 2014

Sample : SUM({<[fis_month] = {'$(=(Month(Addmonths(Forecast Fiscal Month,-1)))'}>}[Stag Forecast Units])

is not working...

PFA..

My data looks like that.........

Re: Need Help to write the expression.

tresB — Wed, 09 Apr 2014 07:13:47 GMT

Then, try like:

SUM({<[Forecast Fiscal Month] = {'$(=(MonthName(Addmonths(Date,-1)))'}>}[Stag Forecast Units]).

Update:

Missed the max(). Try=SUM({<[Forecast Fiscal Month] = {'$(=(MonthName(Addmonths(Max([Forecast Fiscal Month]),-1)))'}>}[Stag Forecast Units]).

Re: Re: Need Help to write the expression.

suvechha_b — Wed, 09 Apr 2014 07:15:13 GMT

The data has to calculated through the period

Jul 2013 Aug 2013 Sep 2013 .......................Jun 2014

Sample : =SUM({<[fis_month] = {$(=Max([Forecast Fiscal Month])-1)}>} [Stag Forecast Units])

is not working...

PFA ...My data looks like that......

Re: Need Help to write the expression.

Wed, 09 Apr 2014 07:15:56 GMT

can you share the sample qvw?

Re: Re: Need Help to write the expression.

its_anandrjs — Wed, 09 Apr 2014 07:16:16 GMT

Put single cots like

=SUM({<[fis_month] = {'$(=Max([Forecast Fiscal Month])-1)'}>} [Stag Forecast Units])

Re: Re: Need Help to write the expression.

its_anandrjs — Wed, 09 Apr 2014 07:19:12 GMT

If you use this

SUM({<[fis_month] = {'$(=(Month(Addmonths(Forecast Fiscal Month,-1)))'}>}[Stag Forecast Units])

use date field and what is your dimension

SUM({<[fis_month] = {"$(=(Month(Addmonths(YourDatefieldHere,-1)))"}>}[Stag Forecast Units])

and use double cots

Re: Need Help to write the expression.

MK_QSL — Wed, 09 Apr 2014 07:19:40 GMT

SUM({<[Forecast Fiscal Month] = {"$(=Date(AddMonths(Max([Forecast Fiscal Month]),1),'MMM YYYY'))"}>}[Stag Forecast Units])

Re: Re: Need Help to write the expression.

suvechha_b — Wed, 09 Apr 2014 07:28:03 GMT

My Sample Model attached.

Re: Re: Need Help to write the expression.

suvechha_b — Wed, 09 Apr 2014 07:38:32 GMT

PFA ...

The sample Model...

Re: Re: Need Help to write the expression.

suvechha_b — Wed, 09 Apr 2014 07:38:42 GMT

PFA ...

The sample Model...

Re: Re: Need Help to write the expression.

suvechha_b — Wed, 09 Apr 2014 07:39:00 GMT

PFA ...

The sample Model...

Re: Need Help to write the expression.

Anonymous — Wed, 09 Apr 2014 07:44:19 GMT

I understood that basing on some filters u should calculate sum. I had a similar situation earlier and I solved it using this expression. I think your req is similar to what I had earlier,please change your code in accordance with the one I pasted below. Here newyear,a field, has a filter and newmonth,another field, has 3 filters. work on it.Place the quotes properly. Hope it helps.

sum

({<newyear={'$(=max(newyear)-1)'},newmonth={'$(=Month(AddMonths(max(Dates),3)))','$(=Month(AddMonths(max(Dates),2)))','$(=Month(AddMonths(max(Dates),1)))'}>}Sales)

Re: Need Help to write the expression.

tresB — Wed, 09 Apr 2014 08:52:59 GMT

What you explain is not what is there in your application; field values are not similar. However, if you can brief about your expected result, might help us help you.

Re: Need Help to write the expression.

Wed, 09 Apr 2014 09:08:34 GMT

Hi Suvechha,

Try creating one inline table, this will be used in assigning numbers to [Forecast Fiscal Month] field because if you try in text box also max([Forecast Fiscal Month]) it won't come because [Forecast Fiscal Month] is text.

Please let us know if it works.

Thanks

Suhas