How to compute a measure in last 6 periods

[Behnamqmarsi]

hi

please help me for this case

How to compute a measure in  last 6 periods with dimensional functions

[MFGF]

hi

please help me for this case

How to compute a measure in  last 6 periods with dimensional functions

How about aggregate([your measure] within set [your set of six period members])

To get the set of the latest six period members for this, you could use the closingPeriod() function to isolate the latest period, and the lastPeriods() function to give you the six periods up to and including this member

eg

lastPeriods (6, closingPeriod ([your Periods level]))

This would then make your overall expression

aggregate([your measure] within set lastPeriods (6, closingPeriod ([your Periods level])))

Of course, this might not be what you're looking for, but from the vague requirement you posted it's my best guess :)

MF.

[Behnamqmarsi]

How about aggregate([your measure] within set [your set of six period members])

To get the set of the latest six period members for this, you could use the closingPeriod() function to isolate the latest period, and the lastPeriods() function to give you the six periods up to and including this member

eg

lastPeriods (6, closingPeriod ([your Periods level]))

This would then make your overall expression

aggregate([your measure] within set lastPeriods (6, closingPeriod ([your Periods level])))

Of course, this might not be what you're looking for, but from the vague requirement you posted it's my best guess :)

MF.

tank you man

excuse me, My English is not good

please tell me
how create filter in this calculated measure(your defined)

aggregate([your measure] within set lastPeriods (6, closingPeriod ([your Periods level])))

[MFGF]

tank you man

excuse me, My English is not good

please tell me
how create filter in this calculated measure(your defined)

aggregate([your measure] within set lastPeriods (6, closingPeriod ([your Periods level])))

Are you displaying this value? If so, just include the expression in a query calculation in your crosstab. Otherwise, can you describe the details of what you're trying to do? What do you want to filter?

MF.

[Behnamqmarsi]

Hi
i explain for you my problem

i have a case study that contain of sales information in different month of years

i have one measure As "Sales Amount"

i need defined a same measure "sales amount" for 6 month period for current member: "6 period measure"

so i create that by calculated query with dimensional functions .

in this stage every things works right and Ok

I defined a filter .
when i used this filter "sales amount measure" i get correct result
my problem is :
filter dose not work for "6 period measure" and did not get  any result.
now i want know how i can apply filter to "6 period measure"when run report

for example:
i want to filter  sellers who have "6 period measure">100000000
unfortunately  i do not understand hot to define appropriate expression for filter function

thank you for your help

[MFGF]

Hi
i explain for you my problem

i have a case study that contain of sales information in different month of years

i have one measure As "Sales Amount"

i need defined a same measure "sales amount" for 6 month period for current member: "6 period measure"

so i create that by calculated query with dimensional functions .

in this stage every things works right and Ok

I defined a filter .
when i used this filter "sales amount measure" i get correct result
my problem is :
filter dose not work for "6 period measure" and did not get  any result.
now i want know how i can apply filter to "6 period measure"when run report

for example:
i want to filter  sellers who have "6 period measure">100000000
unfortunately  i do not understand hot to define appropriate expression for filter function

thank you for your help

Hi

You'd filter your sellers by bringing in a calculated item for the sellers, using a filter function

eg

filter([your Sellers members], [your 6 period measure above] > 100000000)

With a dimensional source, don't use detail filters :)

MF.

[Behnamqmarsi]

Hi

You'd filter your sellers by bringing in a calculated item for the sellers, using a filter function

eg

filter([your Sellers members], [your 6 period measure above] > 100000000)

With a dimensional source, don't use detail filters :)

MF.

hi my friend

excuse me  , i do not understand your means, please explain in detail

tank you

[Behnamqmarsi]

Hi

You'd filter your sellers by bringing in a calculated item for the sellers, using a filter function

eg

filter([your Sellers members], [your 6 period measure above] > 100000000)

With a dimensional source, don't use detail filters :)

MF.

are your means  , i use Slicer???????????????

[MFGF]

are your means  , i use Slicer???????????????

Hi

No - not a slicer. Replace your current set of seller members in the report with a query calculation, and use the expression I outlined.

Cheers!

MF.

[Behnamqmarsi]

Hi
thank you for your guidance
i ask my question the other way

we want sellers who  have (Measure-->"saleAmount") >10000000$ and (Measure-->6 last period saleAmount")<200000000  in a specified month of each year

for example:

in march of 2016 if this condition for seller who was true show us result,not in march of all years that we have as data

_______________________________________
| year|Month|Seller|SalesAmount|6periodAmount|
|        |          |         |                    |                        |
|        |          |         |                    |                        |
|-------------------------------------------------------------|
thank you

[Behnamqmarsi]

 :-[ :-[ :-[ :-[ :-[ :-[ :-[
please help me
:-[ :-[ :-[ :-[

[MFGF]

we want sellers who  have (Measure-->"saleAmount") >10000000$ and (Measure-->6 last period saleAmount")<200000000  in a specified month of each year

for example:

in march of 2016 if this condition for seller who was true show us result,not in march of all years that we have as data

_______________________________________
| year|Month|Seller|SalesAmount|6periodAmount|
|        |          |         |                    |                        |
|        |          |         |                    |                        |
|-------------------------------------------------------------|
thank you

Why didn't you just ask this at the beginning? It has taken six posts over a few days for you to tell us what you really want to do. Wouldn't it have been easier (and ultimately quicker) to spend a few minutes describing what you wanted at the beginning?

Are you creating a list or a crosstab here? Hopefully not a list with a dimensional source. Assuming it's a crosstab, you'd have Year as the outer row heading, Month nested within Year in the row heading, and a Query Calculation nested within Month in the row heading (this will be the filter expression for your Sellers). In the columns you'd have SalesAmount, and a Query Calculation stacked to the right of this (this is your 6PeriodAmount calculation).

The expression for your 6PeriodAmount calculation would be

aggregate([SalesAmount] within set lastPeriods (6, closingPeriod ([your Periods level])))

The expression for your Sellers calculation would be

filter([your Sellers level], [SalesAmount] > 10000000 and [6PeriodAmoiunt] < 200000000)

Because this is nested within the Month members in the row headings, the context would be within that specific month.

:-[ :-[ :-[ :-[ :-[ :-[ :-[
please help me
:-[ :-[ :-[ :-[

This is a forum for Cognos developers to help each other where they can and when they get chance. Just like you, other forum members have a full-time (and often very demanding) day job of their own. If you don't get a reply instantly, it's likely because people are working hard to do their jobs and simply don't have time to monitor the forum 24/7. Being pushy and posting messages like this isn't a very thoughtful thing to do under the circumstances, is it? It implies your needs are greater than everyone else's, and you're trying to push or shame others into replying and fixing your problem.

MF.

[Behnamqmarsi]

it is not  work  :'( :'( :'( :'(

i change that report in crosstab table in report studio

please help me for view cells that    ( Measure-->"saleAmount") >900000$ and (Measure-->"last period saleAmount")<700000)

The filter does not work properly on query calculate measure( last period saleAmount)

my data source is dynamic cube

thanks you

[MFGF]

it is not  work  :'( :'( :'( :'(

i change that report in crosstab table in report studio

please help me for view cells that    ( Measure-->"saleAmount") >900000$ and (Measure-->"last period saleAmount")<700000)

The filter does not work properly on query calculate measure( last period saleAmount)

my data source is dynamic cube

thanks you

Hi,

This isn't what we suggested. We suggested you have Year and Month as row headings, not column headings. The query calculation for your sellers (with the filter expression) would be nested in the row headings to the right of these.

Also, it doesn't look like you have defined the expression for your 6 last period salesamount correctly. Wouldn't this always be greater than Saleasmount? Can you describe what you did here?

MF.

[Behnamqmarsi]

hi
Thank you for your response


I attach a file to explain more clearly


my report in crosstab table in report studio

please help me for view cells that    ( Measure-->"saleAmount") >3000,000$ and (Measure-->"6 last period saleAmount")>10,000,000) or selleres that the above conditions have   per month not all month

The filter does not work properly on query calculate measure(6 last period saleAmount)

my data source is dynamic cube

[Behnamqmarsi]

 :'( :'( :'( :'(
please help me

:'( :'(

[Behnamqmarsi]

i need your help :'( :'( :'( :'( :'( :'( :'( :'( :'( :'(

[Behnamqmarsi]

please see the attache file


please help me

:'( :'( :'( :'(

[Behnamqmarsi]

hi
Thank you for your response


I attach a file to explain more clearly


my report in crosstab table in report studio

please help me for view cells that    ( Measure-->"saleAmount") >3000,000$ and (Measure-->"6 last period saleAmount")>10,000,000) or selleres that the above conditions have   per month not all month

The filter does not work properly on query calculate measure(6 last period saleAmount)

my data source is dynamic cube

please see this final file


please  help me

thanks for helping :'( :'( :'( :'( :'( :'( :'(

[Behnamqmarsi]

please  :-[ :-[ :-[ :-[ :-[ :-[ :-[ :-[

hi
Thank you for your response


I attach a file to explain more clearly


my report in crosstab table in report studio

please help me for view cells that    ( Measure-->"saleAmount") >3000,000$ and (Measure-->"6 last period saleAmount")>10,000,000) or selleres that the above conditions have   per month not all month

The filter does not work properly on query calculate measure(6 last period saleAmount)

my data source is dynamic cube