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

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

etxgmg — Wed, 31 Jul 2019 15:14:11 GMT

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?

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

dplr-rn — Wed, 31 Jul 2019 19:45:03 GMT

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

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

dplr-rn — Wed, 31 Jul 2019 20:01:27 GMT

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)

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

etxgmg — Thu, 01 Aug 2019 09:39:54 GMT

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.

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

dplr-rn — Thu, 01 Aug 2019 11:19:21 GMT

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

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

etxgmg — Sat, 03 Aug 2019 20:56:54 GMT

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/alternate-states-comparative-analysis.htm

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