Set Analysis

[#1]

Hi,

I am trying to write a set analysis expression like below:

=Sum({<FIS_CAL_WEEK_ID ={">=Max(FIS_CAL_WEEK_ID)-12 <=Max(FIS_CAL_WEEK_ID))"}>} TOTAL_QTY)

The objective sum of Total Quantity where it will start from Maximum week number and will show only 13 weeks in the chart.

Thanks

[Sokkorn]

Hi Simky,

Try this

=Sum({<({<FIS_CAL_WEEK_ID = {">=$(=Max(FIS_CAL_WEEK_ID)-12)<=$(=Max(FIS_CAL_WEEK_ID))"} >} TOTAL_QTY)

Regards,

Sokkorn

[maxgro · Accepted solution]

Sum( {$<FIS_CAL_WEEK_ID = {">=$(=Max(FIS_CAL_WEEK_ID)-12)<=$(=Max(FIS_CAL_WEEK_ID))"} >} TOTAL_QTY)

[#4]

Hi Simky,

I've not tested this but I hope it works. I have recently done something similar to create a rolling 12 months. What I will suggest including in your set analysis is ignoring selections on other date fields. In the example below I have added "FIS_YEAR=," to demonstrate what I mean:

=SUM({<FIS_YEAR=, FIS_CAL_WEEK={">=$(=Max(FIS_CAL_WEEK_ID)-12)<=$(=Max(FIS_CAL_WEEK_ID))"}>} TOTAL_QTY)

Regards,

Chris

[#5]

This expression is working, thanks for the help

[#6]

What is reason of adding other fields? If I have 10 ffilters in a dashboard, do I need to add all?

[#7]

try this

=Sum({<({<FIS_CAL_WEEK_ID = {'>=$(=Max(FIS_CAL_WEEK_ID)-12)<=$(=Max(FIS_CAL_WEEK_ID))'} >} TOTAL_QTY)

[#8]

Hi Simky,

If you want to ignore all the selections for all 10 filters, it is better to do like this:

= SUM ({1 <FIS_CAL_WEEK={"> = $ (= Max (FIS_CAL_WEEK_ID) -12) <= $ (= Max (FIS_CAL_WEEK_ID)) "}>} TOTAL_QTY)

Constant 1 - represents the full set of all the records in the application.

[maxgro]

if possible, when you ask a question and you get an answer that proves right to you, mark as helpful or answer, thanks

[#10]

Sure. I have marked your correct answer.