something like a flag to determine daily formula => possible?

[#1]

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

[sunny_talwar]

May be this

If(closingFlag = 1,

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

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

[#3]

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.

[sunny_talwar]

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)

[#5]

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.

[dwforest]

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.

[sunny_talwar]

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)

[#8]

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.