Qlik Community

Qlik Sense App Development

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

paulyeo11
Valued Contributor II

How to get Last year same period Stock Amount ?

Hi All

May i know how to change the below SET expression , to get Last year same period Stock Amount ?


Money(RangeSum(
Above(RangeSum(Above(Sum({<year, month, GL_CODE = {50010}>}[Amount]*1), 0, RowNo()))),
-Sum({<GL_CODE = {50010}>}TOTAL Aggr(Sum({<year, month, GL_CODE = {50010}>}[Amount]*1), YearMonth)))
, $(vMoneyFormatK_GL))

So that my Table 2 have  one more column display LY same period stock amount.

Paul

1 Solution

Accepted Solutions

Re: How to get Last year same period Stock Amount ?

Try this:

Money(RangeSum(Sum({<year, month, GL_CODE = {50010}>}[Amount]*-1),

Sum({<year, month, GL_CODE = {50010}, YearMonth = {"=YearMonth > AddYears(Max(TOTAL YearMonth), -1) and YearMonth <= Max(TOTAL YearMonth)"}>}[Amount]*1))

, $(vMoneyFormatK_GL))


Capture.PNG

8 Replies
paulyeo11
Valued Contributor II

Re: How to get Last year same period Stock Amount ?

My QVF

Re: How to get Last year same period Stock Amount ?

What is the number that you are expecting to see?

paulyeo11
Valued Contributor II

Re: How to get Last year same period Stock Amount ?

LY stock.png

Re: How to get Last year same period Stock Amount ?

Slightly more complicated then I imagined, I will work on this at a later time

Re: How to get Last year same period Stock Amount ?

Try this:

Money(RangeSum(Sum({<year, month, GL_CODE = {50010}>}[Amount]*-1),

Sum({<year, month, GL_CODE = {50010}, YearMonth = {"=YearMonth > AddYears(Max(TOTAL YearMonth), -1) and YearMonth <= Max(TOTAL YearMonth)"}>}[Amount]*1))

, $(vMoneyFormatK_GL))


Capture.PNG

paulyeo11
Valued Contributor II

Re: How to get Last year same period Stock Amount ?

Hi Sunny

Can you also give me last month Amount ? it should be 1.413 million.

Paul

Re: How to get Last year same period Stock Amount ?

Try this:

Money(RangeSum(Sum({<year, month, GL_CODE = {50010}>}[Amount]*-1),

Sum({<year, month, GL_CODE = {50010}, YearMonth = {"=YearMonth > AddMonths(Max(TOTAL YearMonth), -1) and YearMonth <= Max(TOTAL YearMonth)"}>}[Amount]*1))

, $(vMoneyFormatK_GL))

paulyeo11
Valued Contributor II

Re: How to get Last year same period Stock Amount ?

I move this to the top , as i am working on this

Community Browser