Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
I have an excel file and i need to calculate the number of values for the last 4 weeks (based in today date)
For example we are in 26 December so i need to have the numbers of values for the last 4 weeks
how to do it
Thank you
If you already have Week as Field, You can define as
Sum({<Week = {">=$(=Max(Week-4))<=$(=Max(Week))"}>} Measure) // I assume, Week is in Number format
For your case, Transformation needed using CrossTable() function. After done that, You need to arrange like
Sum({<DateField = {">=$(=Week(Date(Max(Date#(DateField, 'DD-MMM-YY')),'DD-MM-YYYY'))-4)<=$(=Week(Date(Max(Date#(DateField, 'DD-MMM-YY')),'DD-MM-YYYY')))"}>} Measure)
Or, If you have only specific dates without historical data use this
Sum({<DateField = {">=$(=Date(Max(Date#(DateField, 'DD-MMM-YY'))-4,'DD-MM-YYYY'))<=$(=Date(Max(Date#(DateField, 'DD-MMM-YY')),'DD-MM-YYYY'))"}>} Measure)