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.
Not applicable

Creating a Cumulative LY vs CY

Hi,

I am trying to generate a graph with Cumulative values by month for CY vs LY.

For CY i am using below and it works fine(Working)

rangesum(above(

Sum({< DateNum={">=$(=Num(YearStart(Today(),0,10)))<$(=Num((Today())))"}

,OpportunityStageName ={'Closed Won'}>} [Weighted_Booking__Amt]/1000000),0,rowno()))


For LY I am not getting the rite result(Not Working)

RangeSum(Above

(Sum({<Year=, Month=, Quarter=, Week=, DateField=, DateNum={">=$(=Num(YearStart(Today()-365,0,10)))<$(=Num(Yearend(Today()-365,0,10)))"}

,OpportunityStageName ={'Closed Won'} >} Weighted_Booking__Amt/1000000)),0,RowNo())

Please help.


Tags (1)
1 Solution

Accepted Solutions
MVP
MVP

Re: Creating a Cumulative LY vs CY

Your brackets/parantheses seems to be misplaced in your second expression:

RangeSum(Above(

Sum({< DateNum={">=$(=Num(YearStart(Today(),0,10)))<$(=Num((Today())))"}

,OpportunityStageName ={'Closed Won'}>} [Weighted_Booking__Amt]/1000000),0,rowno()))


For LY I am not getting the rite result(Not Working)

RangeSum(Above(

Sum({<Year=, Month=, Quarter=, Week=, DateField=, DateNum={">=$(=Num(YearStart(Today()-365,0,10)))<$(=Num(Yearend(Today()-365,0,10)))"}

,OpportunityStageName ={'Closed Won'} >} Weighted_Booking__Amt/1000000)),0,RowNo())

After the 1000000 in the first expression you have one of them. In the second expression there are two of them.

At the end of the expression there is two versus one...

I have highlighted in red and black ....

6 Replies
jolivares
Valued Contributor

Re: Creating a Cumulative LY vs CY

First post an example of your data for helping you... Explain what you need specificaly

MVP
MVP

Re: Creating a Cumulative LY vs CY

Your brackets/parantheses seems to be misplaced in your second expression:

RangeSum(Above(

Sum({< DateNum={">=$(=Num(YearStart(Today(),0,10)))<$(=Num((Today())))"}

,OpportunityStageName ={'Closed Won'}>} [Weighted_Booking__Amt]/1000000),0,rowno()))


For LY I am not getting the rite result(Not Working)

RangeSum(Above(

Sum({<Year=, Month=, Quarter=, Week=, DateField=, DateNum={">=$(=Num(YearStart(Today()-365,0,10)))<$(=Num(Yearend(Today()-365,0,10)))"}

,OpportunityStageName ={'Closed Won'} >} Weighted_Booking__Amt/1000000)),0,RowNo())

After the 1000000 in the first expression you have one of them. In the second expression there are two of them.

At the end of the expression there is two versus one...

I have highlighted in red and black ....

MVP & Luminary
MVP & Luminary

Re: Creating a Cumulative LY vs CY

Hi,

Try this expression

Rangesum(Above

(Sum({<Year=, Month=, Quarter=, Week=, DateField=, DateNum={">=$(=Num(YearStart(Today(),-1,10)))<$(=Num(Yearend(Today(),-1,10)))"}

,OpportunityStageName ={'Closed Won'} >} Weighted_Booking__Amt/1000000),0,RowNo()))

Issue with Paranthesis, correct it.

Regards,

Jagan.

Not applicable

Re: Creating a Cumulative LY vs CY

Thanks Peter and Jagan...It was indeed Paranthesis.

MVP & Luminary
MVP & Luminary

Re: Creating a Cumulative LY vs CY

Hi,

Also never use -365 to get previous year, for leap year it is 366 days.  Some times there may be issue, so use below expression to get YearStart of previous year.

YearStart(Today(),-1,10).


Regards,

jagan.

Not applicable

Re: Creating a Cumulative LY vs CY

Thanks for the tip Jagan.