Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Qlik® Product Spotlight: Discover what’s possible. Get more from our products.
See for yourself. Register today.
slondono
Contributor II

Cumulative sum (Set Analysis)

Hi everybody,

I have a problem with a set analysis.

I need to sum values depends on which month is selected. For example

I have two fields: Final Cost and PPTO

For January: Sum(Final Cost) / Sum({<Month={feb,mar,apr}>}PPTO) // but i need this dynamic

The rule is: Sum of Final Cost of the current month / Sum of PPTO of the next three months

Hope you can help me

Tags (1)
3 Replies
aarkay29
Valued Contributor

Re: Cumulative sum (Set Analysis)

Something Like This

Sum({<DateField={">=$(=MonthStart(Max(today())))"}>}Final Cost) / Sum({<DateField={">$(=MonthEnd(Max(today())))<=$(MonthEnd(AddMonths(Max(today()),4)))"}>}PPTO)

Re: Cumulative sum (Set Analysis)

Next three months? Are you assuming that  Jan is selected?

Khan_Mohammed
Honored Contributor II

Re: Cumulative sum (Set Analysis)

Are you using Master Calendar? I believe that will be easier.

//This will be next three month excluding current month.

if(InQuarterToDate(TempDate, today(),-3), 1, 0) as NextQTRFlag,

if(InMonthToDate(TempDate, today(),0), 1, 0) as CurMonthFlag,

Insert above syntax in your master script if you have one.

And

sum({<CurMonthFlag={1}>}[Final Cost])/sum({<NextQTRFlag={1}>}[PPTO])

This should work I guess.