cancel
Showing results for 
Search instead for 
Did you mean: 

Dynamic filter on measure based on prompt

Former Member
0 Kudos

Dear Community,

I´m trying to filter a measure based on the prompt. The user is able to select an intervall for the time period (e.g. January 2015 - September 2015) in prompt. In the Report the user shall see the months 01-09 for one measure but also see the month 09 for the second measure.

But the user also should be able to select another Intervall of month (e.g. 01.2015-08.2015) then the second measure should be for 08.2015.

Is there a specific function which I can use?

Thanks and kind regards,

Ann-Christin

Accepted Solutions (1)

Accepted Solutions (1)

amitrathi239
Active Contributor
0 Kudos

Hi,

If you have Month object in the report which populating the all months based on the user response then use Last function to capture the value like 08 or 09.

V Month=Last([Month])

After that for measure create a variable like.

V Last Month value=[Measure] where ([Month]=Last([Month]))

Amit

Former Member
0 Kudos

Hi Amit Kumar,

Thank you for your fast reply.

If I create a variable like V Last Month Value = [Measure] where ([Month]=Last[Month]), I´ll get back the sum for the whole Intervall (in this case from 01.2015-09.2015). But I just want to see the last month (or better to say the Maximum of month selected).

Kind regards,

Ann-Christin

amitrathi239
Active Contributor
0 Kudos

Hi,

try with this.

=Last([Sales revenue]) In  ([Year])

or

=[Sales revenue] Where ([Month]=Last([Month]) In ([Year]))

Replace the Year object from your dimension object where dimension object values are common across the months.

Amit

Former Member
0 Kudos

Hi,

great, the first variable is already working!

But I have a further requirement to the issue of variable. At the Moment the variable gives me the sum for the last month back. But I want to see the result for every Distribution channel too.

I give you an example:

The left table shows you the issue of the variable and the right one shows you the result how it should be.

Kind regards,

Ann-Christin

amitrathi239
Active Contributor
0 Kudos

Hi,

in this case try with this.

=Last([Sales revenue]) In  ([Distribution channel])


Amit

Former Member
0 Kudos

Hi,

this is not working. The result of this variable is the sum of highest Distribution channel. In my example the sum for Distribution Channel 99.

I have also tried a variable like the following, but then i get back the sum for every distribution channel for the whole interval (01.2015-09.2015)

V= ([Sales Revenue] In [Distribution Channel]) Where ([Month]=Last([Month]))

Ann-Christin

amitrathi239
Active Contributor
0 Kudos

Hi,

try this.

Create variable V Last Month=Last([Month])In ([Distribution Channel])

V Last Month value=([Sales revenue]) Where ([Month]=[V Last Month])

See attached screenshot.Created sample table from efashion universe data.

If this will not work then share the full screenshot of your table data.

Amit

mhmohammed
Active Contributor
0 Kudos

Hey Ann,

Create the below 2 variables as measures.

  • v_Max Month =If([Month]=Max([Month]) In Report) Then 1 Else 0

  • v_Amt for Max Month =[Amount] Where (([v_Max Month]=1) In ([Month]))

Voila


You'll see that for all the Months & Distribution Channels, the measure v_Amt for Max Month shows the Amount from Max Month for that Distribution Channel.

Let me know if that helps.


Thanks,

Mahboob Mohammed

Former Member
0 Kudos

Hey Mahoob Mohammed,

this is working! Thank you very much!

Kind regards,

Ann-Christin

mhmohammed
Active Contributor
0 Kudos

Great Ann!!! Glad I was able to help.

Thanks,
Mahboob Mohammed

Answers (0)