Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
jenmclean
New Contributor II

Calculate percentage based on the # of Tuesday's in a month

I have the following expression which gives me a sum for food cost. I then take this sum and divide by net sales to get a Food Cost %.

Accounting wants to see this percentage averaged by the number of Tuesdays in the month to date. So for instance, the number of Tuesday for May to Date is 2, next week it will be 3 etc.

Any ideas on how best to tackle this problem?

Expression is Food Cost $/Net Sales (this expression is just the food cost)

IF (Month=Month(Today()),
Sum({$<Day={">=$(=1)<=$(=Max(Day(Today()-2)))"}, KEY_FormID={'DRSbwyPOS'}, KEY_Report={128}, KEY_Store-={210, 325}>}Amount),
//Else
Sum({$<KEY_FormID={'DRSbwyPOS'}, KEY_Report={128}, KEY_Store-={210, 325}>}Amount))

1 Solution

Accepted Solutions
MVP
MVP

Re: Calculate percentage based on the # of Tuesday's in a month

Maybe like discussed here for the weekday count?

¿How to count Saturdays in date range?

edit:

Tuesday count would look like

=Div(WEEKDAY(Monthstart(today())-2)+1-Monthstart(today())+Today(),7)

2 Replies
MVP
MVP

Re: Calculate percentage based on the # of Tuesday's in a month

Maybe like discussed here for the weekday count?

¿How to count Saturdays in date range?

edit:

Tuesday count would look like

=Div(WEEKDAY(Monthstart(today())-2)+1-Monthstart(today())+Today(),7)

jenmclean
New Contributor II

Re: Calculate percentage based on the # of Tuesday's in a month

Thanks so much for your quick response

Community Browser