How to write different formulae for each cell?

[Shubham_Deshmukh]

	LY	LY LM	YTD	YTD LM
A	*
B
C

* = Formulae

I have such situation, in which neither first row nor first column is dimension.

We have to manually write formula for 1 row 1 column , 2 row 1 column , ......... matrix like this.

Please help.

[Shubham_Deshmukh]

Right now I m trying to do it by taking 2 dimensions

[shraddha_g]

What 2 Dimension you have used now?

Instead of Pick() you will have to use If statement.

for Ex.

If(wildmatch(Valuelist('A','B','C'),'A') and Wildmatch(Valuelist('c','d','e','f'),'c') , Expression for A and c Combination,

If(wildmatch(Valuelist('A','B','C'),'A') and Wildmatch(Valuelist('c','d','e','f'),'d') , Expression for A and d Combination,

so on for other combinations

))..

[Shubham_Deshmukh]

Thanks.

Ok let me try this now and

1st dimension = valuelist('A','B')

2nd dimension= valuelist('c','d','e','g')

[shraddha_g]

yes

[Shubham_Deshmukh]

Thanks Shraddha, Its partially working

See, it is working  for

When I m going for other dimension like

Its not working.

Nested if is not working properly.

[shraddha_g]

Share the expression you used.

[Shubham_Deshmukh]

My structure is,

If(){

if{},if{},if{},if{}

},

If(){

if{},if{},if{},if{}

}....

=If(Wildmatch(Valuelist('PRO','HFRP','WEB'),'PRO') ,

if(Wildmatch(Valuelist('Gross','Net','L2S','CPS'),'Gross'),

Count(If(Posted >=date(floor(monthstart(addmonths(Today(),-1)))) AND Posted<=date(floor(monthend(addmonths(Today(),-1)))),aContractID)),

If( Wildmatch(Valuelist('Gross','Net','L2S','CPS'),'Net'),

(Count(If(Posted>=date(floor(monthstart(addmonths(Today(),-1)))) AND Posted<=date(floor(monthend(addmonths(Today(),-1)))),aContractID))) - count(if(nContractStatus='Cancelled' and CancellationPosted >=date(floor(monthstart(addmonths(Today(),-1)))) AND CancellationPosted<=date(floor(monthend(addmonths(Today(),-1)))), aContractID)),

If(Wildmatch(Valuelist('Gross','Net','L2S','CPS'),'L2S'),

((Count(If(LEAD_CREATED_DATE >=date(floor(monthstart(addmonths(Today(),-1)))) AND DATE<=date(floor(monthend(addmonths(Today(),-1)))),Lead_ID)))/

Count(If(Posted>=date(floor(monthstart(addmonths(Today(),-1)))) AND Posted<=date(floor(monthend(addmonths(Today(),-1)))),aContractID))),

If(Wildmatch(Valuelist('Gross','Net','L2S','CPS'),'CPS'),

'-')))),

If(Wildmatch(Valuelist('PRO','HFRP','WEB'),'HFRP') ,

if(Wildmatch(Valuelist('Gross','Net','L2S','CPS'),'Gross'),

Count(If(Posted>=date(floor(monthstart(addmonths(Today(),-1)))) AND Posted<=date(floor(monthend(addmonths(Today(),-1)))),aContractID)),

If(Wildmatch(Valuelist('Gross','Net','L2S','CPS'),'Net'),

(Count(If(Posted>=date(floor(monthstart(addmonths(Today(),-1)))) AND Posted<=date(floor(monthend(addmonths(Today(),-1)))),aContractID))) - count(if(nContractStatus='Cancelled' and CancellationPosted >=date(floor(monthstart(addmonths(Today(),-1)))) AND CancellationPosted<=date(floor(monthend(addmonths(Today(),-1)))), aContractID)),

If(Wildmatch(Valuelist('Gross','Net','L2S','CPS'),'L2S'),

((Count(If(DATE >=date(floor(monthstart(addmonths(Today(),-1)))) AND DATE<=date(floor(monthend(addmonths(Today(),-1)))),Lead_ID)))/

Count(If(Posted>=date(floor(monthstart(addmonths(Today(),-1)))) AND Posted<=date(floor(monthend(addmonths(Today(),-1)))),aContractID))),

If(Wildmatch(Valuelist('Gross','Net','L2S','CPS'),'CPS'),

'-' )))),

If(Wildmatch(Valuelist('PRO','HFRP','WEB'),'WEB') ,

If(  Wildmatch(Valuelist('Gross','Net','L2S','CPS'),'Gross'),

Count(If(Posted>=date(floor(monthstart(addmonths(Today(),-1)))) AND Posted<=date(floor(monthend(addmonths(Today(),-1)))),aContractID)),

If(Wildmatch(Valuelist('Gross','Net','L2S','CPS'),'Net'),

(Count(If(Posted>=date(floor(monthstart(addmonths(Today(),-1)))) AND Posted<=date(floor(monthend(addmonths(Today(),-1)))),aContractID))) - count(if(nContractStatus='Cancelled' and CancellationPosted >=date(floor(monthstart(addmonths(Today(),-1)))) AND CancellationPosted<=date(floor(monthend(addmonths(Today(),-1)))), aContractID)),

If( Wildmatch(Valuelist('Gross','Net','L2S','CPS'),'L2S'),

((Count(If(DATE >=date(floor(monthstart(addmonths(Today(),-1)))) AND DATE<=date(floor(monthend(addmonths(Today(),-1)))),Lead_ID)))/

Count(If(Posted>=date(floor(monthstart(addmonths(Today(),-1)))) AND Posted<=date(floor(monthend(addmonths(Today(),-1)))),aContractID))),

If(Wildmatch(Valuelist('Gross','Net','L2S','CPS'),'CPS'),

'-'

))))

)))

[shraddha_g]

It doesn't seem any issue..

Is it possible to share sample app?

There might be some Bracket issue

[Shubham_Deshmukh]

Soryy my structure is ,

If(){

if{},if{},if{},if{}

,

If(){

if{},if{},if{},if{}

}}....

[Shubham_Deshmukh]

No its not possible