topic Re: How to write different formulae for each cell? in QlikView

How to write different formulae for each cell?

Shubham_Deshmukh — Mon, 11 Sep 2017 07:28:25 GMT

	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.

Re: How to write different formulae for each cell?

Anil_Babu_Samineni — Mon, 11 Sep 2017 07:30:36 GMT

This?

If(Match(FieldName, 'LY') and Match(FieldName1, 'A'), Formulae,

If(Match(FieldName, 'LY') and Match(FieldName1, 'B'), Formulae, ....))

Re: How to write different formulae for each cell?

neha_shirsath — Mon, 11 Sep 2017 07:39:19 GMT

You are taking table only?

Because here, if you write code for particular dimension field like A,B,C then if field value increases then that will become issue, rather than you can use text object

Re: How to write different formulae for each cell?

Shubham_Deshmukh — Mon, 11 Sep 2017 08:37:20 GMT

A,B,C and LY, YTD LY etc are not the feilds.We will have to write it manually.

Re: How to write different formulae for each cell?

shraddha_g — Mon, 11 Sep 2017 08:43:49 GMT

You can try as below:

Dimension : Valuelist('A','B','C')

Measure 1 - LY

pick(Wildmatch(Valuelist('A','B','C'),'A','B','C'),

Expression1 for A,

Expression1 for B,

Expression1 for C

)

Measure 2 - LY LM

pick(Wildmatch(Valuelist('A','B','C'),'A','B','C'),

Expression2 for A,

Expression2 for B,

Expression2 for C

)

Measure 3 - YTD

pick(Wildmatch(Valuelist('A','B','C'),'A','B','C'),

Expression3 for A,

Expression3 for B,

Expression3 for C

)

Measure 4 - YTD LM

pick(Wildmatch(Valuelist('A','B','C'),'A','B','C'),

Expression4 for A,

Expression4 for B,

Expression4 for C

)

Re: How to write different formulae for each cell?

Anil_Babu_Samineni — Mon, 11 Sep 2017 08:46:01 GMT

Yes, Those are not the fields but we can achieve the same

Re: How to write different formulae for each cell?

Shubham_Deshmukh — Tue, 12 Sep 2017 03:54:12 GMT

Thank you so so much Shraddha.Its working.

Regards,

Shubham

Re: How to write different formulae for each cell?

Shubham_Deshmukh — Tue, 12 Sep 2017 06:34:42 GMT

A,B,c,d,e,g will be my string only

Now I have such Pivot table structure and same situation as before.

It worked in Straight table.

How to do it in PIVOT Table?

Re: How to write different formulae for each cell?

shraddha_g — Tue, 12 Sep 2017 06:42:44 GMT

Do you mean to say you will be having 4 different expressions for these pairs?

Re: How to write different formulae for each cell?

Shubham_Deshmukh — Tue, 12 Sep 2017 06:51:42 GMT

Ya,It is same as before but 4 valuelist variables will have one common name

Re: How to write different formulae for each cell?

Shubham_Deshmukh — Tue, 12 Sep 2017 06:52:28 GMT

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

Re: How to write different formulae for each cell?

shraddha_g — Tue, 12 Sep 2017 06:58:54 GMT

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

))..

Re: How to write different formulae for each cell?

Shubham_Deshmukh — Tue, 12 Sep 2017 07:02:01 GMT

Thanks.

Ok let me try this now and

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

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

Re: How to write different formulae for each cell?

shraddha_g — Tue, 12 Sep 2017 07:13:48 GMT

yes

Re: How to write different formulae for each cell?

Shubham_Deshmukh — Tue, 12 Sep 2017 09:42:19 GMT

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.

Re: How to write different formulae for each cell?

shraddha_g — Tue, 12 Sep 2017 09:46:08 GMT

Share the expression you used.

Re: How to write different formulae for each cell?

Shubham_Deshmukh — Tue, 12 Sep 2017 09:57:14 GMT

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'),

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'),

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'),

'-'

))))

)))

Re: How to write different formulae for each cell?

shraddha_g — Tue, 12 Sep 2017 09:59:40 GMT

It doesn't seem any issue..

Is it possible to share sample app?

There might be some Bracket issue

Re: How to write different formulae for each cell?

Shubham_Deshmukh — Tue, 12 Sep 2017 10:01:11 GMT

Soryy my structure is ,

If(){

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

If(){

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

}}....

Re: How to write different formulae for each cell?

Shubham_Deshmukh — Tue, 12 Sep 2017 10:02:54 GMT

No its not possible