Getting last count of the week for a weekly graph

Question & Answer
, , ,
1

I’m making a bar graph with the x-axis being fiscal week. I’d like for the bars to show me the volume of a calculated measure from the last day of the week. However, I’ve setup the function: lastvalue and it’s not working properly. It sums up all the values from the fiscal week as the bar and I only want the very last day to show as the bar. So if my fiscal week goes from Oct 15 - 21, I’d like to see the value on the 21st for that bar and so on for a weekly bar graph.
Does anybody have any function that would allow that to happen? I’ve tried this for last value and it’s still summing everything up from the week:
lastValue({Total Marketable}, [{kpi_date} ASC], [{week_desc}])

2

hi @kstory

can you try with maxOver() like the below?

ifelse( {kpi_date} = maxOver( {kpi_date}, [truncDate( ‘WK’, {kpi_date} )], PRE_FILTER), {Total Marketable}, null)

kind regards,
Wakana

3

Thanks for the suggestion! I am getting a syntax error because my measure is a calculated measure. Do you know how to circumvent that? It won’t let me use my Total Marketable field in this equation because behind the scenes that field is two fields being summed together.

4

hi @kstory

in that case, can you try Total Marketable calc field to use sumOver() ?

sumOver(field1+field2, [], PRE_FILETER)

kind regards,
Wakana

5

Yes! The total marketable is a sum of these two fields: rack_count and fullpriceonly_count
So my final calculation would look like this?
sumOver({rack_count} + {fullpriceonly_count}, [{fiscal_week_num}], PRE_FILTER)
Would I then use this for my graph? Or use the output field in the ifelse statement?

6

if you have fiscal_week_num field already, I think the calc should be like the below.

ifelse({kpi_date} = maxOver({kpi_date}, [{fiscal_week_num}], PRE_FILTER), sumOver({rack_count} + {fullpriceonly_count}, [], PRE_FILTER), null)

can you try and let me know if that works or not, please.

kind regards,
Wakana

7

I tried that function and unfortunately it’s still summing up my values each week in the graph. I’ve even tried adjusting the final output to max, count, sum, etc. and all return the wrong counts I’m looking for.

8

hi @kstory

would you be able to provide sample data and screenshot you are seeing with data summed up?

kind regards,
Wakana