
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
something like a flag to determine daily formula => possible?
Hi,
I am fairly new to Qlik Sense/Qlikview and with the help of books/google/youtube I was able to create my first apps sucessfully.
However, maybe due to the lack of some basic conceptual understanding I can not solve this problem - any ideas are higly appreciated 🙂
I would like to create a daily sales report with sales per day, per month, ytd, etc. So far no problem thanks to set analysis.
But if today is 29th of june I want to use a different formula than if today is the 30th of june because the 30th would be month-end closing. (month-end closing could be on last day of actual month, first day of new month or any day of new month).
Is it possible to use a 'closingFlag' column in my excel data to create something like (i know this example makes no sense, only to give example):
if todays date has closingFlag={1},
sum({$<SDate={'>=$(=MonthStart(Today()))<=$(=MonthEnd(Today()))'}>}Sales),
sum({$<SDate={'>=$(=MonthStart(Today()))<=$(=Today())'}>}Sales)
what is the correct syntax for smthg like this or is there a better way to solve this?
the report will then be sent automatically with nprinting that is why i need some logic in my app to determine which formula should be used.
Thank you in advance
regards
E.
SDate | Sales | closingFlag |
---|---|---|
01.01.2017 | 100 | |
x | xyz | |
31.05.2017 | 200 | 1 |
01.06.2017 | 5 | |
29.06.2017 | 8 | |
30.06.2017 | 99 | 1 |
01.07.2017 | 25 | |
31.07.2017 | 14 | |
01.08.2017 | 26 | |
02.08.2017 | 88 | 1 |

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
May be this
If(closingFlag = 1,
Sum({$<SDate={'>=$(=MonthStart(Today()))<=$(=MonthEnd(Today()))'}>}Sales),
Sum({$<SDate={'>=$(=MonthStart(Today()))<=$(=Today())'}>}Sales))

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi Sunny,
thank you for your reply.
I tried your solution with a shortened version of my expression which adds up all sales records from 01.01.2017 - 23.06.2017 (= today):
If(closingFlag = 1,
sum({$<SDate = {'>=$(=YearStart(Today())) <=$(=Today())'}>}Sales),
0)
but it seems to make no difference wether I put a 1 or a 0 in the cell closingFlag.
SDate | Sales | closingFlag |
---|---|---|
01.06.2017 | 100 | 0 |
22.06.2017 | 50 | 0 |
23.06.2017 | 80 | 1 |
24.06.2017 | 100 | 0 |
result should be: Sales = 230 (because there is flag '1' today, which is the 23rd of june)
if the app is opened tomorrow the result should be: Sales = 0 (because not flag '1' on 24th of june)
is this a syntax problem or is it not possible to check the flag on a daily basis and switch between then & else?
regards
E.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
May be this....
If(Only({<SDate = {"$(=Date(Today(), 'DD.MM.YYYY'))"}>} ClosingFlag = 1,
Sum({$<SDate = {"$(='>=' & Date(YearStart(Today()), 'DD.MM.YYYY') & '<=' & Date(Today(), 'DD.MM.YYYY'))"}>}Sales), 0)

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
hi Sunny,
with your formula I only get: - as result.
I tried different variation but I am not able to come up with an solution.
The problem seems not to be related to date format as my expressions work fine when I use them without if clause.
any ideas what I am doing wrong?
have a nice weekend
E.


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Try:
sum({$<SDate = {'>=$(=Date(YearStart(Today())),'DD.MM.YYYY') <=$(=Date(Today(),'DD.MM.YYYY'))'},closingFlag={1}>} Sales)
You could omit the 'DD.MM.YYYY' if that is set as your default date format, Qlik is sometimes weird with dates in calculations in Set expressions. The default behavior for a date is a dual value (int, string in "date" format). When in a Set Expression I think it sometimes does not realize it should be a date and the function returns a number which when compared to the date field (as a string) does not match. Explicit conversion removes this doubt.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
I might have missed a closing parenthesis here... Not sure if you fixed it or not
If(Only({<SDate = {"$(=Date(Today(), 'DD.MM.YYYY'))"}>} ClosingFlag) = 1,
Sum({$<SDate = {"$(='>=' & Date(YearStart(Today()), 'DD.MM.YYYY') & '<=' & Date(Today(), 'DD.MM.YYYY'))"}>}Sales), 0)

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
thank you Sunny and David for your help.
Unfortunately, I am still not able to get this think working.
for now I will simply create 2 apps with different formulas and therefore circumvent using if-then based on a flag.
I will update this thread if I can come up with an solution.
regards
E.
