Show last 6 months when selecting

[Anonymous]

hello,

i would like to show the last 6 months when selecting a date.

date is my dimension and its format is yyyymm

sales is an expression

sum({1<date={'>=$(=AddMonths(Max(date),-5)) <=$(=(Max(date)))'}>}sales)

it is not working and i do not understand why.

i hope you can help me.

[vishsaggi]

1 is the identifier in your set to ignore current selections.

Try removing 1 and select date and see if you getting any results?

= sum({<date={ ' >= $(=AddMonths(Max(date),-5)) <= $(=Max(date)) ' }>} sales)

[Anonymous]

not working

[vishsaggi]

Try this

= sum({<date={ " >= $(= Date(AddMonths(Max(date),-5))) <= $(=Max(date)) " }>} sales)

OR

= sum({<date={ " $(= '>=' & AddMonths(Max(date),-5))  & '<=' & Max(date)) " }>} sales)

Can you share a sample app if possible?

[Anonymous]

mm i think that date is making it wrong.

¿it is always my dimension ?

[Anonymous]

here i attached you some sample data

[vishsaggi]

Can you send me correct dates, when i used max date i got 210607 is this correct ? If i do addmonths -5 it is giving 210602 which is not there in the data.?

[Anonymous]

here you have with different data.

[vishsaggi · Accepted solution]

When ever we deal with dates from excel we need to convert them to proper dates and work on them, that is how i do personally else things wont work properly. So, i have created my own excel sheet and worked on it. I have also created couple of variables for ease. Just press ctrl+alt+v to view them. Please find attached the excel sheet that replicates your data and sample file i worked on. Let me know if this is what you are looking for?

[Anonymous]

it works fine. thank you