SET Analysis Question

[#1]

Hello All, I am trying to calculation the % for Each Month/Year. and please find the below steps:

Dimension: Month/Year [May-2015, Apr-2015 etc]

Expression:

Basic:  Sum of ItemID if ItemID avg LeadTime - Prev 6 Months avg LeadTime range -1 to 1 then 1 else 0  / [Count of ItemID for Month]

How Can I write this on SET Analysis.

[sunny_talwar]

Its hard to understand your expression. Would you be able to put it in words of what you are trying to do?

Best,

Sunny

[#3]

I have fields like [Month/Year] ,[DateKey] , [ItemID] , [LeadTime]

I am creating the line chart showing LeadTimeGap % measure for each Month/Year. So Dimension is Month/Year. I want to show all the Month/Year even though I select the Month/Year on LB

Please find below Nr & Dr parts:

Denominator is pretty Straight Forward: Total No of ItemID's in the May-2015 month

          Count( {<[Month/Year]=>} DISTINCT [ItemID] ) --> I added the SET to ignore the [Month/Year] selections

Numerator calculation is little complex. I need to count ItemID only if ( ItemID current Month avg LeadTime ) - (ItemID Prev 6 Months avg LeadTime) must b/n -1 to 1.

I am not sure how to write Nr expression in SET analysis.

[#4]

Any thoughts.

[Anonymous]

It may or may not work with set analysis.  Besides, not enough information to give you the precise answer.  From the available information, here is the directions:

count(distinct

if((CurrentMonthAvgLeadTime - Prev6MonthAvgLeadTime) >-1

  and (CurrentMonthAvgLeadTime - Prev6MonthAvgLeadTime) < 1,

ItemID))

You have to substitute the

CurrentMonthAvgLeadTime

and

Prev6MonthAvgLeadTime

with your actual expressions of course.

Or, upload an example of your app for a more specific answer.

[#6]

Hi Michael, Please find the attached qvw for reference:

Below is the formula for avg Lead Time

avg LeadTime = avg( Actual_Lead_Time)

Note: because of huge data, I attached for 3months

[Gysbert_Wassenaar]

I have no idea what you're trying to calculate. Can you give an example using actual numbers? Perhaps an excel file with some example calculations?

[#8]

Gysbert, Please find the attached excel with sample data and I explained my calculation in the other tab as well. The data tab is my data and I explained calculations on OUTPUT tab. Please let me know in case of any concerns.

[Gysbert_Wassenaar]

Good, that excel file certainly helped to explain your question. See attached qvw for a solution.