topic Re: Set Expression with time modifier in QlikView

Set Expression with time modifier

robinwiman — Mon, 26 Feb 2018 12:59:31 GMT

I have the following expression:

Sum (if ((Year = CurrentYear) and (Week <= ($(VECKANRTMP)+10)), SalesBudEUR))

And I want to turn it into a set expression instead. But I'm not sure about how to integrate the +10 modifier.

Re: Set Expression with time modifier

Anil_Babu_Samineni — Mon, 26 Feb 2018 13:05:28 GMT

I am assuming, These are variables? Perhaps this?

Sum ({<Year = {$(CurrentYear)}, Week={"<=($(VECKANRTMP)+10)"} SalesBudEUR)

Sum ({<Year = {$(CurrentYear)}, Week={"$('<=' & VECKANRTMP+10)"} SalesBudEUR)

Re: Set Expression with time modifier

robinwiman — Mon, 26 Feb 2018 13:22:07 GMT

CurrentYear and VECKANRTMP are both variables, yes.

Unfortunately, the solutions you provided does not work. It shows no data at all.

Perhaps I should explain my question further:

The Sum (if ((Year = CurrentYear) and (Week <= ($(VECKANRTMP)+10)), SalesBudEUR))is a sum of the annual budget for the current year (Year = CurrentYear) and then displaying the budget per week in a bar chart for the coming 10 weeks (Week <= ($(VECKANRTMP)+10)).

Re: Set Expression with time modifier

Anil_Babu_Samineni — Mon, 26 Feb 2018 13:32:14 GMT

My bad, I missed $ sign here, May be?

Sum ({<Year = {$(=CurrentYear)}, Week={"<=$(=VECKANRTMP)+10)"} SalesBudEUR)

Re: Set Expression with time modifier

robinwiman — Mon, 26 Feb 2018 13:37:20 GMT

Still no luck

I tried to close the expressions as well:

Sum ({<Year = {$(=CurrentYear)}, Week={"<=$(=VECKANRTMP)+10)" >} SalesBudEUR)

Re: Set Expression with time modifier

stigchel — Mon, 26 Feb 2018 13:47:55 GMT

Try with

=Sum ({<Year = {$(=CurrentYear)}, Week={"<=$(=VECKANRTMP+10)"} >} SalesBudEUR)

Re: Set Expression with time modifier

robinwiman — Mon, 26 Feb 2018 13:51:35 GMT

There we go!

It works as intended - Thanks a lot. Also big thanks to @Anil Babu!

Re: Set Expression with time modifier

stigchel — Mon, 26 Feb 2018 13:55:19 GMT

A good tip for debugging these kind of problems is to create a straight table where you leave the label for the expression blank. The header of the table will diaplay how the $ sign expansions are evaluated like