Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Join us in NYC Sept 4th for Qlik's AI Reality Tour! Register Now
cancel
Showing results for 
Search instead for 
Did you mean: 
jorditorras
Creator
Creator

Set analysis previous day

Dear Gurus,

I'm developing an application in QlikSense related to Sales, where every morning I load data until the previous day (yesterday). I'm trying to generate two kind of set analysis related to sales and dates:

Sum (Sales) of last day loaded to the App (Yesterday)

Sum (Sales) of the previous day of the last day loaded to the App (two days ago)

I've tried several Set Analysis and I've checked in all the forums but I haven't found any applicable solution.

Could please anyone help me with this please?

Thank you very much in advance!!!

Regards,

Jordi

1 Solution

Accepted Solutions
petter
Partner - Champion III
Partner - Champion III

Presuming you have a field named Date containing the dates...

Yesterday should be:

Sum( {<Date={'$(=Date(Today(1)-1))'}>} Sales)

The day before yesterday should be:

Sum( {<Date={'$(=Date(Today(1)-2))'}>} Sales)

These expression might need further qualification or sophistication depending on where they are used.

For instance if you have a more fully built out calendar breaking the date field you have into different date categories like Year, Month, Quarter etc.... You might have to nullify selections in these fields that a user might have selected in as they will block the selections on Date if you don't.

Like this:

Sum( {<Date={'$(=Date(Today(1)-1))'},Year=,Quarter=,Month=,Week=>} Sales)

The bolded part of the set expression above will remove user selections that might be active so they don't block the first part of the set expression.

You could also disregard ALL selections by using the 1 set identfier within the set expression like this:

Sum( {1<Date={'$(=Date(Today(1)-1))'}>} Sales)

View solution in original post

4 Replies
petter
Partner - Champion III
Partner - Champion III

Presuming you have a field named Date containing the dates...

Yesterday should be:

Sum( {<Date={'$(=Date(Today(1)-1))'}>} Sales)

The day before yesterday should be:

Sum( {<Date={'$(=Date(Today(1)-2))'}>} Sales)

These expression might need further qualification or sophistication depending on where they are used.

For instance if you have a more fully built out calendar breaking the date field you have into different date categories like Year, Month, Quarter etc.... You might have to nullify selections in these fields that a user might have selected in as they will block the selections on Date if you don't.

Like this:

Sum( {<Date={'$(=Date(Today(1)-1))'},Year=,Quarter=,Month=,Week=>} Sales)

The bolded part of the set expression above will remove user selections that might be active so they don't block the first part of the set expression.

You could also disregard ALL selections by using the 1 set identfier within the set expression like this:

Sum( {1<Date={'$(=Date(Today(1)-1))'}>} Sales)

jorditorras
Creator
Creator
Author

Thank you very much Petter!!!!!!!!!!

jorditorras
Creator
Creator
Author

By the way, I had to use the second option.

Sum( {<Date={'$(=Date(Today(1)-1))'},Year=,Month=,Week=>} Sales)

petter
Partner - Champion III
Partner - Champion III

You're welcome - Happy to help