Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
QlikWorld 2020: Join us May 11 - 14, 2020 in Phoenix, AZ. Register early and save \$400. Learn More
New Contributor II

How to write script expressions that sets certain months in a formula?

I am a complete newbie to Qlik Sense Desktop and don't understand the syntax and how it applies to my case.

I have made a Bar diagram with Product as dimension and now need a measure like this:

Sum(Sales Amount) for the months April to November / Sum(Sales Amount) for the months December to March

(where / is divided by, the normal algebraic operator) This will show the products that sell relatively bad during the period Dec-March.

and this using DateKey.autoCalendar.Month, I guess.

What would the complete expression for the script editor be, including the formula with set expressions?

Labels (1)
• Set Analysis

1 Solution

Accepted Solutions
New Contributor II

Re: How to write script expressions that sets certain months in a formula?

I gave up on this. It ought to be the calendar thing that somehow causes me problems, and I am completely new to Qlik.

But I came around the problem by using alternate states (winter, notwinter) for the month ranges which I set by filter boxes instead. Then I used the states in the expressions for the measures (my calculations) with set-expressions.

Sum({winter} [Sales Amount]) / Sum({notwinter} [Sales Amount])

I just followed the below guide, and its example, and then it worked!

Many thanks, Dilipranjith, for your effort to help. Highly appreciated!

5 Replies Partner

Re: How to write script expressions that sets certain months in a formula?

try something like below
Sum({<DateKey.autoCalendar.Month={'April','May'}>}Sales) Partner

Re: How to write script expressions that sets certain months in a formula?

Expanding on my suggestion. use set analysis. it is a very powerful part of qlik

Sum({<DateKey.autoCalendar.Month -= {'Dec','Jan','Feb','Mar'}>}Sales)
/ Sum({<DateKey.autoCalendar.Month = {'Dec','Jan','Feb','Mar'}>}Sales)

Denominator - used set analysis to denote i want months Dec-Mar
on the numerator note i used -= this means all months apart from Dec-Mar (you could just list out the months as well)
New Contributor II

Re: How to write script expressions that sets certain months in a formula?

Many thanks, but something is wrong. My formula

Sum({<[Invoice Date].autoCalendar.Month -= {'Dec','Jan','Feb','Mar'}>} [Sales Amount]) /
Sum({<[Invoice Date].autoCalendar.Month = {'Dec','Jan','Feb','Mar'}>} [Sales Amount])

doesn't show anything.

If I just do
Sum( [Sales Amount])
I get a perfect bar graph, but if I do for instance
Sum({<[Invoice Date].autoCalendar.Month = {'Jan'}>} [Sales Amount])
or
Sum({<[Invoice Date].autoCalendar.Month = {1}>} [Sales Amount])
then I get nothing. Partner

Re: How to write script expressions that sets certain months in a formula?

Not sure. I suggest creating an actual master calendar rather than the auto generated one

New Contributor II

Re: How to write script expressions that sets certain months in a formula?

I gave up on this. It ought to be the calendar thing that somehow causes me problems, and I am completely new to Qlik.

But I came around the problem by using alternate states (winter, notwinter) for the month ranges which I set by filter boxes instead. Then I used the states in the expressions for the measures (my calculations) with set-expressions.

Sum({winter} [Sales Amount]) / Sum({notwinter} [Sales Amount])

I just followed the below guide, and its example, and then it worked!

Many thanks, Dilipranjith, for your effort to help. Highly appreciated!