topic Re: SET Analysis Question in QlikView

SET Analysis Question

Fri, 05 Jun 2015 18:10:03 GMT

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.

Re: SET Analysis Question

sunny_talwar — Fri, 05 Jun 2015 18:12:40 GMT

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

Best,

Sunny

Re: SET Analysis Question

Fri, 05 Jun 2015 18:32:11 GMT

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.

Re: SET Analysis Question

Sat, 06 Jun 2015 12:52:59 GMT

Any thoughts.

Re: SET Analysis Question

Anonymous — Sat, 06 Jun 2015 19:00:52 GMT

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.

Re: SET Analysis Question

Tue, 09 Jun 2015 20:20:39 GMT

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

Re: SET Analysis Question

Gysbert_Wassenaar — Thu, 11 Jun 2015 18:50:53 GMT

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?

Re: SET Analysis Question

Sun, 14 Jun 2015 02:10:33 GMT

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.

Re: SET Analysis Question

Gysbert_Wassenaar — Sun, 14 Jun 2015 16:55:55 GMT

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