2 Replies Latest reply on Jul 14, 2017 4:05 AM by Prashant mallya

    showing salesYTD, previous week sales, occupancy% based on a filter or parameter control

    raghavendra N

      Hi ,

       

      I am trying to find sale present week, previous week, Sales YTD, Occupancy % based on the filter/ parameter value.

      I have attached the Workbook and sample data below. In sheet one I am trying to use the Filter and Sheet 2 I am trying to use parameter control.

       

      I need either of them to work.


      For sales YTD parameter i have used the below formula but I am getting an error cant compare date and integer.

       

      Sales YTD Parameter=  sum( if [Previous Weeks] > 1 and [Previous Weeks] <= datepart('week',[Week End Date]) then [Num Sales] end)

       

      And SImilarly for the Sales YTD and SALES Last week using week Filter on sheet 1 I have used

       

      SALES YTD=  sum( if [Week Filter] >= 1 and [Week Filter] < datepart('week',[Week End Date]) then [Num Sales] end)

       

      SALES LW= sum( if [Week Filter] = datepart('week',-1,[Week End Date]) then [Num Sales] end)

       

      The occupancy% change with week filter but it doesnot work with the paramete.

       

      Can anyone of you look into it and suggest or help If I am doing something wrong.