Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
paulyeo11
Valued Contributor II

My SET expression on work for year 2016 , How to get 404k for dec 20125 ? Now display 0

Hi All

I have below expression work fine for April 2016 current month sales :-

=num(sum(

{

$

<

year = {$(=max(year))},

month =

{

"<=$(=max(

{

<year={$(=max(year))}>

}

month)-0)>$(=max(

{

<

year={$(=max(year))

}

>

} month)-1)"

}

>

}

sales), '$#,##0;($#,##0)')

My issue is when i change my current month = march 2016 , may i know how to make Dec 2015 display as 404K ?

Paul

1 Solution

Accepted Solutions

Re: My SET expression on work for year 2016 , How to get 404k for dec 20125 ? Now display 0

Try this expression:

=Num(Sum({$<date = {"$(='>=' & Date(MonthStart(Max(date), -3), 'DD/MM/YYYY') & '<=' & Date(MonthEnd(Max(date), -3), 'DD/MM/YYYY'))"}, year, month>} sales), '$#,##0;($#,##0)')

I would also change the all other months to be driven based on date instead of month and year because month and year complicate things when you are moving from one year to another

4 Replies
paulyeo11
Valued Contributor II

Re: My SET expression on work for year 2016 , How to get 404k for dec 20125 ? Now display 0

my qv doc

Re: My SET expression on work for year 2016 , How to get 404k for dec 20125 ? Now display 0

Try this expression:

=Num(Sum({$<date = {"$(='>=' & Date(MonthStart(Max(date), -3), 'DD/MM/YYYY') & '<=' & Date(MonthEnd(Max(date), -3), 'DD/MM/YYYY'))"}, year, month>} sales), '$#,##0;($#,##0)')

I would also change the all other months to be driven based on date instead of month and year because month and year complicate things when you are moving from one year to another

paulyeo11
Valued Contributor II

Re: My SET expression on work for year 2016 , How to get 404k for dec 20125 ? Now display 0

Hi Sunny

Thank you very much , I just manage to convert sales to rEVENUE by refer to old post. i think now my SET analysis have improve a bit , thank you for your encouragement.

=Num(Sum({$

<

date = {"$(='>=' & Date(MonthStart(Max(date), -1), 'DD/MM/YYYY') & '<=' & Date(MonthEnd(Max(date), -1), 'DD/MM/YYYY'))"},

year,

month,

rEVENUE={"rEVENUE"}

}

>

}Amount*1), '$#,##0;($#,##0)')

Re: My SET expression on work for year 2016 , How to get 404k for dec 20125 ? Now display 0

Awesome, I am glad you are making attempts to make improvements at your end

Community Browser