7 Replies Latest reply: Apr 17, 2018 10:25 AM by Mark Little

Current Month , Current and previous years

K Kv Apr 16, 2018 11:22 AM

Hello,

My chart look like this

Measures that I use :

Sum({$<[Date]={">=$(=AddYears(Date(MonthStart(Today())),0)) <=$(=AddYears(Date(Today()),0))"}>}FactSoldAmount) --- for 2018

Sum({$<[Date]={">=$(=AddYears(Date(MonthStart(Today())),-1)) <=$(=AddYears(Date(Today()),-1))"}>}FactSoldAmount) --- for 2017

Sum({$<[Date]={">=$(=AddYears(Date(MonthStart(Today())),-2)) <=$(=AddYears(Date(Today()),-2))"}>}FactSoldAmount) ----- for 2016

I want to have current month information as default but when I select the month it should show the selected month's data in these years.

I wrote this but it doesn't work

If( GetSelectedCount(Month)=0,

Sum(Sales),

Sum({$<[Date]={">=$(=AddYears(Date(MonthStart(Today())),0)) <=$(=AddYears(Date(Today()),0))"}>}Sales)

) --------- for 2018

But it shows me the whole year's data.

Re: Current Month , Current and previous years

youssef belloum Apr 16, 2018 11:58 AM (in response to K Kv)

EDITED:

Hi,

try this:

If( GetSelectedCount(Month)=0,
Sum(Sales),
Sum({$<[Date]={">=$(=Date(MonthStart(Max(Date))))<=$(=Max(Date))"}>}Sales)
Like Show 0 Likes (0)

Actions
- Re: Current Month , Current and previous years
  
  K Kv Apr 17, 2018 3:33 AM (in response to youssef belloum)
  
  It shows me the whole year, but I want to show only the current month as default
  
  Also, When I select month it shows me only one year, not all the 3 years .
  
  Like Show 0 Likes (0)
  
  Actions
  - Re: Current Month , Current and previous years
    
    youssef belloum Apr 17, 2018 3:38 AM (in response to K Kv)
    
    Would you be able to attach some data (which represent your table structure) ?
    
    Like Show 0 Likes (0)
    
    Actions
    - Re: Current Month , Current and previous years
      
      K Kv Apr 17, 2018 9:57 AM (in response to youssef belloum)
      
      Sorry, I can't
      the code that you suggested for 2018, works but incorrectly .
      For example, today is April and I have data in January, March, February and April. If I choose May, it shouldn't show anything
      Also, other years doesn't work
      
      Like Show 0 Likes (0)
      
      Actions
Re: Current Month , Current and previous years

Mark Little Apr 17, 2018 10:11 AM (in response to K Kv)

Hi,

I think that you need something like,

If( GetSelectedCount(Month)<>0,
Sum(Sales),
Sum({$<[Date]={">=$(=Date(MonthStart(Max(Date))))<=$(=Max(Date))"}>}Sales)

So you are saying if a month is selected the sum(Sales) Else ....

Mark
Like Show 0 Likes (0)

Actions
- Re: Current Month , Current and previous years
  
  K Kv Apr 17, 2018 10:17 AM (in response to Mark Little )
  
  It doesn't work. For example, if I write only this
  Sum({$<[Date]={">=$(=Date(MonthStart(Max(Date))))<=$(=Max(Date))"}>}Sales)
  
  it shows current month information, but if I select month, for example May, What does it show?
  
  We don't have the information in May in 2018
  
  Like Show 0 Likes (0)
  
  Actions
  - Re: Current Month , Current and previous years
    
    Mark Little Apr 17, 2018 10:25 AM (in response to K Kv)
    
    Hi,
    
    When you remove the if i would expext to see what you are seeing.
    The $ in your set analysis means inculde any selections, so you select May your date field is only looking at dates in May.
    If you always want to show May, Then maybe change the $ to 1, but that would always ignore all selections.
    
    I personally, make flags in my script to mark current month, Current year dates to make the Set analysis quicker and easier.
    
    Mark
    
    Like Show 0 Likes (0)
    
    Actions

Go to original post