hi
please help me for this case
How to compute a measure in last 6 periods with dimensional functions
Quote from: Behnamqmarsi on 11 Aug 2016 07:21:07 AM
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.
Quote from: MFGF on 11 Aug 2016 07:59:23 AM
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])))
Quote from: Behnamqmarsi on 11 Aug 2016 08:09:00 AM
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.
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
Quote from: Behnamqmarsi on 11 Aug 2016 04:01:08 PM
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.
Quote from: MFGF on 12 Aug 2016 03:38:51 AM
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
Quote from: MFGF on 12 Aug 2016 03:38:51 AM
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???????????????
Quote from: Behnamqmarsi on 12 Aug 2016 04:59:57 AM
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.
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
:-[ :-[ :-[ :-[ :-[ :-[ :-[
please help me
:-[ :-[ :-[ :-[
Quote from: Behnamqmarsi on 14 Aug 2016 10:26:56 AM
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.
Quote from: Behnamqmarsi on 15 Aug 2016 07:13:21 AM
:-[ :-[ :-[ :-[ :-[ :-[ :-[
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.
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
Quote from: Behnamqmarsi on 21 Aug 2016 06:45:03 AM
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.
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 help me
:'( :'(
i need your help :'( :'( :'( :'( :'( :'( :'( :'( :'( :'(
please see the attache file
please help me
:'( :'( :'( :'(
Quote from: Behnamqmarsi on 26 Aug 2016 12:56:30 AM
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 :'( :'( :'( :'( :'( :'( :'(
please :-[ :-[ :-[ :-[ :-[ :-[ :-[ :-[
Quote from: Behnamqmarsi on 26 Aug 2016 12:56:30 AM
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