Skip to main content

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
CUSTOMERS ONLY: Now accepting customer applications for the 2023 Luminary Program: SUBMIT NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
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
sunny_talwar

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
Author

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?

sunny_talwar

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

Not applicable
Author

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!

sunny_talwar

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
Author

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.

sunny_talwar

No problem at all