Qlik Community

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
etxgmg
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)
1 Solution

Accepted Solutions
etxgmg
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! 

https://help.qlik.com/en-US/sense/April2019/Subsystems/Hub/Content/Sense_Hub/Visualizations/alternat... 

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

5 Replies
Partner
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
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)
etxgmg
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
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

etxgmg
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! 

https://help.qlik.com/en-US/sense/April2019/Subsystems/Hub/Content/Sense_Hub/Visualizations/alternat... 

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