topic Re: Trellis Chart in QlikView

Trellis Chart

Anonymous — Fri, 20 Nov 2015 23:40:54 GMT

I have a trellis chart with Sum(Amount) which represents yearly sales as an expression. The application is attached.

I using the following expression to calculate the MAT for each applicant:

if(Year = 2015,Num(sum({<[Date Dispensed]={">=$(=addmonths(MonthEnd(max([Date Dispensed]))+1,-12)) <=$(=max([Date Dispensed]))"}, Applicant = {[SANOFI AVENTIS], GLAXOSMITHKLINE, PFIZER, NOVARTIS, [TORRENT],[NOVO NORDISK], RANBAXY, IPCA, ALCON, GLENMARK, MYLAN, VARICHEM, [CIPLA], ALLERGAN}>}[MedAid Amount]+Shortfall), '$##,##0.00'), if(Year = 2014, Num(sum({<[Date Dispensed]={">=$(=addmonths(MonthEnd(max([Date Dispensed]),)+1,-24)) <=$(=addmonths(MonthEnd(max([Date Dispensed])),-12))"}, Applicant = {[SANOFI AVENTIS], GLAXOSMITHKLINE, PFIZER, NOVARTIS, TORRENT,[NOVO NORDISK], RANBAXY, IPCA, ALCON, GLENMARK, MYLAN, VARICHEM, [CIPLA], ALLERGAN}>}[MedAid Amount]+Shortfall), '$##,##0.00'), if(Year = 2013, Num(sum({<[Date Dispensed]={">=$(=addmonths(MonthEnd(max([Date Dispensed]),)+1,-36)) <=$(=addmonths(MonthEnd(max([Date Dispensed])),-24))"}, Applicant = {[SANOFI AVENTIS], GLAXOSMITHKLINE, PFIZER, NOVARTIS, TORRENT,[NOVO NORDISK], RANBAXY, IPCA, ALCON, GLENMARK, MYLAN, VARICHEM, [CIPLA], ALLERGAN}>}[MedAid Amount]+Shortfall), '$##,##0.00'))))

I am getting some results which are however lower than expected. Can any one look at the above expression and application and advise accordingly.

Message was edited by: Christopher Chitemerere

Re: Trellis Chart

Marcio_Campestrini — Mon, 23 Nov 2015 12:15:19 GMT

Hi Christopher

Try to break your expression in small expressions to identifiy what is the result in each step. This works for me when I have a situation like yours.

Marcio

Re: Trellis Chart

Anonymous — Mon, 23 Nov 2015 18:24:07 GMT

Many thanks Marcio

Re: Trellis Chart

Marcio_Campestrini — Mon, 31 Oct 2016 18:56:14 GMT

Have you solved your problem? If you did, please choose the correct answer and help us to keep community focused.

Re: Trellis Chart

Anil_Babu_Samineni — Tue, 01 Nov 2016 00:30:47 GMT

If you have forecast data then how you are calculating the CU, LY & LLY. I recommend you to create aggression functions and it send ok to me. What was the issue you are facing? Is there Any typo error because I don't have license for local client

Re: Trellis Chart

Marcio_Campestrini — Mon, 29 May 2017 14:12:01 GMT

Got you issue solved? Please mark helpful/correct answers.