7 Replies Latest reply: Jun 25, 2017 12:26 PM by Edgar Kim

# 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.

regards

E.

SDateSalesclosingFlag
01.01.2017100
xxyz
31.05.20172001
01.06.20175
29.06.20178
30.06.2017991
01.07.201725
31.07.201714
01.08.201726
02.08.2017881
• ###### Re: something like a flag to determine daily formula => possible?

May be this

If(closingFlag = 1,

Sum({\$<SDate={'>=\$(=MonthStart(Today()))<=\$(=MonthEnd(Today()))'}>}Sales),

Sum({\$<SDate={'>=\$(=MonthStart(Today()))<=\$(=Today())'}>}Sales))

• ###### Re: something like a flag to determine daily formula => possible?

Hi Sunny,

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.

SDateSalesclosingFlag
01.06.20171000
22.06.2017500
23.06.2017801
24.06.20171000

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.

• ###### Re: something like a flag to determine daily formula => possible?

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)

• ###### Re: something like a flag to determine daily formula => possible?

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.

• ###### Re: something like a flag to determine daily formula => possible?

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)

• ###### Re: something like a flag to determine daily formula => possible?

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.

• ###### Re: something like a flag to determine daily formula => possible?

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.