Need help on logic

[NavinReddy]

Dear Experts,

i want to right a query when i will get duplicates on FactID i want to merge 3 months of data into single line

rest of FactID its working fine.

i just want to display all three months into single line.it's happening only when i will have duplicates on FactIDs

please find the attachment

Any script modifications please suggest me

Manythanks,

Niru

[rubenmarin]

Hi Niranjan, QV splits rows based on dimensions, if there are different values for Trier3 benchmark and Tier1 limit it will split results based on the different dimension values.

You can try this removing this two dimensions. it will automatically show in one row. If the values are the same it will show in one row...There is a value for this two columns wich can represent both rows?,

[NavinReddy]

Hi Ruben,

Thanks you so much your reply

yes you're correct,

Here i want to take current month dimension values based on month selections

Best Regards,

Niru

[rubenmarin]

Ok, now I get where your problem is, the expression from the past months returns the dimensión values from that month, using aggr() in the calculated dimensión can fix the values returned by the dimensión.

Try with this dimensions:

Tier3

Aggr(if(ELE_VALUE_TYPE_CD = '-',

if(ELE_VALUE_TYPE_CD = 'PCT',Num((GREEN_RAG),'##.00%'),

  if(ELE_VALUE_TYPE_CD = 'QTY',Num(GREEN_RAG))),

if(ELE_VALUE_TYPE_CD = 'PCT',Num((HIGH_GREEN_RAG),'##.00%')& '' & Num((GREEN_RAG),'##.00%'),

  if(ELE_VALUE_TYPE_CD = 'QTY',Num(HIGH_GREEN_RAG)& '  ' & Num(GREEN_RAG)))), FCT_TMPLT_ID, FCT_SETUP_ID, FCT_DESC, PROC_NM)

Tier1

=Aggr(if(ELE_VALUE_TYPE_CD = '-',

if(ELE_VALUE_TYPE_CD = 'PCT',Num((RED_RAG),'##.00%'),if(ELE_VALUE_TYPE_CD = 'QTY',Num(RED_RAG))),

if(ELE_VALUE_TYPE_CD = 'PCT', Num((HIGH_RED_RAG),'##.00%')&' '&Num((RED_RAG),'##.00%'),

if(ELE_VALUE_TYPE_CD = 'QTY',Num(HIGH_GREEN_RAG)&' '&Num(RED_RAG)))), FCT_TMPLT_ID, FCT_SETUP_ID, FCT_DESC, PROC_NM)

I just enclosed your actual dimensión in an Aggr with your previous dimensions as parameters.

Hope this helps!

[NavinReddy]

Hi Ruben,

Thank you so much its working fine

There is a problem here prior months also i have some value, please help me to get prior month's value

Many Thanks,

Niru

[rubenmarin · Accepted solution]

Hi Niranjan, sorry for delay.

Your problem with expressions seems is only a typo:

if(ELE_VALUE_TYPE_CD = 'PCT',Num(sum({<Month=, MonthYear={"$(=Date($(vMonth)-10,'MMM YY'))"}>}FCT_ACTL_VALUE_QT),'##.00%'),

if(ELE_VALUE_TYPE_CD = 'QTY',sum({<Month=, MonthYear={"$(=Date($(vMonth-10),'MMM YY'))"}>}FCT_ACTL_VALUE_QT)))

The parenthesys is wrong, it should be:

if(ELE_VALUE_TYPE_CD = 'PCT',Num(sum({<Month=, MonthYear={"$(=Date($(vMonth)-10,'MMM YY'))"}>}FCT_ACTL_VALUE_QT),'##.00%'),

if(ELE_VALUE_TYPE_CD = 'QTY',sum({<Month=, MonthYear={"$(=Date($(vMonth)-10,'MMM YY'))"}>}FCT_ACTL_VALUE_QT)))

In this case 'Past month' is really a '10 days before'?

You can also use AddMonths function:

MonthYear={"$(=Date(AddMonths($(vMonth), -1),'MMM YY'))"}

And 2 months before can be

MonthYear={"$(=Date(AddMonths($(vMonth), -2),'MMM YY'))"}

[NavinReddy]

Hi Ruben,

your rocks, Thank you so much its working

ManyThanks,

Niranjan

[NavinReddy]

Hi Ruben,

here i have posted one issue can't we write (AND, OR) function in the same expression

please help me

How to calculate consecutive months count

Best Regards,

Niru