Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Not applicable

How do I write the correct formula to find the quantity sold in the last six weeks?

Hello,

I am trying to find the quantity of items sold in the last six weeks. Here is what my formula looks like:

=Count({<Max(Week([Year Month]))={>=Max(Week([Year Month])-6)}>}[Unit Sold])

When I enter that formula in, it doesn't show the correct value. Can anyone help me fix my formula?

Thank you

7 Replies
MVP
MVP

Re: How do I write the correct formula to find the quantity sold in the last six weeks?

Do you have a field Date or Year Week? It would be better to use that in your case:

Count({<Date = {"$(='>=' & Date((Max(Date) - 42), 'YourDateFieldFormat'))"}>}[Unit Sold]))

Not applicable

Re: How do I write the correct formula to find the quantity sold in the last six weeks?

No, it doesn't have those fields, I only see the field [Year Month] in the QlikView account that I am using for where I am interning. I was trying to see where I can add in those fields but I couldn't see the option for me to add it. Maybe it is a permission issue?

MVP
MVP

Re: How do I write the correct formula to find the quantity sold in the last six weeks?

You don't have access to the script? You can add these fields in the back end of the application

Not applicable

Re: How do I write the correct formula to find the quantity sold in the last six weeks?

Yeah they only created an account for me where I log in using the web browser. So I just need to have access, then I can add them right? If that's the case, I will let them know. Also, to add the field, do you go to the Edit Script -> Insert -> Load Statement -> Load Inline and then add the field? Sorry, I just want to make sure but thanks!

MVP
MVP

Re: How do I write the correct formula to find the quantity sold in the last six weeks?

Not an inline load. Whichever table has Year Month in it, add these two statements in there I guess (although not that simple cause we won't know what Year Month is a text or dual field, but you can try)

LOAD [Year Month],

          Date([Year Month]) as Date

FROM/Resident blah blah...

Not applicable

Re: How do I write the correct formula to find the quantity sold in the last six weeks?

That makes so much sense to me. I will definitely try this out. I'm not sure if I will finish this by today since I'm getting off soon, but I will during this week and verify it and let you know.

MVP
MVP

Re: How do I write the correct formula to find the quantity sold in the last six weeks?

No problem at all