Set expression in a set expression

[jakobjosef]

Hi everyone!

Does someone know if it is possible to use a set expression an a set expression?

I want to show Sales starting from 01.10.2017 until the last day of previous month.

So far, I used this expression.

Every month, I have to update the "Last day of the previous month" in many tables and charts. So now I thought I could create a variable with "the last day of the month", where I just have to update the date once in the variable.

I tried:

vLastDayofPreviousMonth = 43737 (which is 30.09.2018)

Sum({$<date= {'<43008 < $(vLastDayofPreviousMonth) } > } Sales)

but it didnt work. Maybe someone has a hint for me to solve this problem?

Thank you very much

Jakob

[sunny_talwar]

May be try this

Sum({$<date = {"$(='>' & 43008 & '<' & Num(MonthStart(Today()) - 1))"}>} Sales)

[YoussefBelloum · Accepted solution]

Hi,

you can try something like this (100% dynamic):

=sum({<date={">$(=AddYears(MonthStart(Today()),-1))<$(=MonthEnd(Today(),-1))"}>}Sales)

PS: verify the format of your date field, it should be exactly the same than the output of the functions inside the variables

[jakobjosef]

Hi youssef,

thank you very much, this solved my problem perfectly!

I don't know yet: When do I use double comma (") and when single comma (')?

Is there any suggestion to learn Set Expression beside the QlikView-manual?

Thank you again!

Jakob

[tresesco]

Have a look here: Quotes in Set Analysis

[YoussefBelloum]

here it is Jakob, the above link

Don't forget to close the thread if it is ok for you.

good luck

[robert99]

Hi Jacob

I had a similar requirement recently

But the user wanted to select one  MonthYear and have a Month total shown and also a YTD figure shown from 1/10/17 to say 30/9/18 (when selecting MonthYear Sept 2018.) But to show 1/10/18 to 31/10/18 when selecting Oct 2018. Or 1/10/16 to 31/8/17 when selecting Aug 17 etc.

I did this using MonthAgo. Its another way of achieving the same thing without having to change the variable each month

Relative Calendar Fields

-SUM({<

YearFin = { "=$(=(YearFin))"}

,MonthsAgo = { ">=$(=min(MonthsAgo))  "}, MonthYear  =

>}ActualValue)

-SUM({<

,MonthsAgoYTD = { ">=$(=MonthsAgoYTD)"}   , MonthYear  

,fYear = {"$(=(fYear)-1)"}

>}ActualValue)

[jakobjosef]

perfect, thank you very very much!

[jakobjosef]

to mark the correct answer is closing the thread, right?

[YoussefBelloum]

Yes, already done