topic Need help on logic in QlikView

Need help on logic

NavinReddy — Sun, 26 Jun 2016 09:03:10 GMT

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

Re: Need help on logic

rubenmarin — Sun, 26 Jun 2016 09:55:30 GMT

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?,

Re: Need help on logic

NavinReddy — Sun, 26 Jun 2016 12:23:30 GMT

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

Re: Need help on logic

rubenmarin — Sun, 26 Jun 2016 17:18:55 GMT

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!

Re: Need help on logic

NavinReddy — Sun, 26 Jun 2016 17:35:05 GMT

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

Re: Need help on logic

rubenmarin — Mon, 27 Jun 2016 15:47:01 GMT

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'))"}

Re: Need help on logic

NavinReddy — Mon, 27 Jun 2016 20:31:27 GMT

Hi Ruben,

your rocks, Thank you so much its working

ManyThanks,

Niranjan

Re: Need help on logic

NavinReddy — Mon, 27 Jun 2016 20:35:31 GMT

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