Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

paulyeo11
Valued Contributor II

How to display as 1 when GP% CY > LY ?


Hi All


Now my Table 1 display correctly:-

SBU.....LY......CY

G3-SL...0.56....0.44

G4-CL...0.18....0.82

G1-GT...0.16....0.84

G2-AP...0.00....1.00

G5-RW...0.00....1.00


May i know how to make the above display as below , notice that when GP% CY > LY CY=1:-

SBU.....LY......CY

G3-SL...0.00....1.00

G4-CL...0.00....1.00

G1-GT...0.00....1.00

G2-AP...0.70....0.30

G5-RW...0.60....0.40


Thank


Paul

1 Solution

Accepted Solutions
paulyeo11
Valued Contributor II

Re: How to display as 1 when GP% CY > LY ?

Hi Anil

Yesterday , take me 4 hour to figure , thank you very much , my solution as below :-

CY Expression :-

if( $(vGpVar) >1,1,$(vGpVar))

LY expression :-

1-if( $(vGpVar) >1,1,$(vGpVar))

vGpVar :-

(

(

Sum({$<year = {$(=Max(year)-0)}, month = {"<=$(=Max({<year={$(=Max(year)-0)}, sales = {'*'}>} month))"}>}sales)

-

Sum({$<year = {$(=Max(year)-0)}, month = {"<=$(=Max({<year={$(=Max(year)-0)}, sales = {'*'}>} month))"}>}cost)

)

/

Sum({$<year = {$(=Max(year)-0)}, month = {"<=$(=Max({<year={$(=Max(year)-0)}, sales = {'*'}>} month))"}>}sales)

)



/



(

(

Sum({$<year = {$(=Max(year)-1)}, month = {"<=$(=Max({<year={$(=Max(year)-1)}, sales = {'*'}>} month))"}>}sales)

-

Sum({$<year = {$(=Max(year)-1)}, month = {"<=$(=Max({<year={$(=Max(year)-1)}, sales = {'*'}>} month))"}>}cost)

)

/

Sum({$<year = {$(=Max(year)-1)}, month = {"<=$(=Max({<year={$(=Max(year)-1)}, sales = {'*'}>} month))"}>}sales)

)

Paul Yeo

3 Replies

Re: How to display as 1 when GP% CY > LY ?

Why last 2 rows become .7 and .6

paulyeo11
Valued Contributor II

Re: How to display as 1 when GP% CY > LY ?

Hi Anil

Yesterday , take me 4 hour to figure , thank you very much , my solution as below :-

CY Expression :-

if( $(vGpVar) >1,1,$(vGpVar))

LY expression :-

1-if( $(vGpVar) >1,1,$(vGpVar))

vGpVar :-

(

(

Sum({$<year = {$(=Max(year)-0)}, month = {"<=$(=Max({<year={$(=Max(year)-0)}, sales = {'*'}>} month))"}>}sales)

-

Sum({$<year = {$(=Max(year)-0)}, month = {"<=$(=Max({<year={$(=Max(year)-0)}, sales = {'*'}>} month))"}>}cost)

)

/

Sum({$<year = {$(=Max(year)-0)}, month = {"<=$(=Max({<year={$(=Max(year)-0)}, sales = {'*'}>} month))"}>}sales)

)



/



(

(

Sum({$<year = {$(=Max(year)-1)}, month = {"<=$(=Max({<year={$(=Max(year)-1)}, sales = {'*'}>} month))"}>}sales)

-

Sum({$<year = {$(=Max(year)-1)}, month = {"<=$(=Max({<year={$(=Max(year)-1)}, sales = {'*'}>} month))"}>}cost)

)

/

Sum({$<year = {$(=Max(year)-1)}, month = {"<=$(=Max({<year={$(=Max(year)-1)}, sales = {'*'}>} month))"}>}sales)

)

Paul Yeo

Re: How to display as 1 when GP% CY > LY ?

Great, you solve by ur self only. You can mark your response as correct answer to close this thread

Community Browser