topic Re: Set expression in a set expression in QlikView

Set expression in a set expression

jakobjosef — Tue, 02 Oct 2018 14:32:55 GMT

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

Re: Set expression in a set expression

sunny_talwar — Tue, 02 Oct 2018 14:35:40 GMT

May be try this

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

Re: Set expression in a set expression

YoussefBelloum — Tue, 02 Oct 2018 14:39:44 GMT

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

Re: Set expression in a set expression

jakobjosef — Wed, 03 Oct 2018 08:47:11 GMT

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

Re: Set expression in a set expression

tresB — Wed, 03 Oct 2018 08:55:37 GMT

Have a look here: Quotes in Set Analysis

Re: Set expression in a set expression

YoussefBelloum — Wed, 03 Oct 2018 08:58:28 GMT

here it is Jakob, the above link

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

good luck

Re: Set expression in a set expression

robert99 — Wed, 03 Oct 2018 09:26:22 GMT

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)

Re: Set expression in a set expression

jakobjosef — Wed, 03 Oct 2018 09:34:46 GMT

perfect, thank you very very much!

Re: Set expression in a set expression

jakobjosef — Wed, 03 Oct 2018 09:36:05 GMT

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

Re: Set expression in a set expression

YoussefBelloum — Wed, 03 Oct 2018 09:41:10 GMT

Yes, already done