topic Re: How to achieve the below task? in QlikView

How to achieve the below task?

narayanamsn — Wed, 08 Jun 2016 15:22:44 GMT

Hi all,

I have the below scenario. Have a set of values for 3 months.

I want to get a count of values which are less than the average of all values for the month..

LOAD * INLINE [

Month, Sale

Jan, 2

Jan, 9

Jan, 4

Jan, 5

Jan, 6

Jan, 8

Jan, 1

Feb, 2

Feb, 3

Feb, 4

Feb, 5

Feb, 8

Mar, 6

Mar, 8

Mar, 1

Mar, 2

Mar, 3

];

How to plot a graph with Month as dimension and Count of values that are less than monthly average as a measure...

Avg of Jan 5, Feb 6, Mar 4 and overall is 5

The observation that I got when I wrote the code is it is for Feb it is not comparing the value against average of Feb (6) but comparing the values against the overall average 5..

The same is observed for all the aggregation functions.. Instead of comparing with the measure of that period , gets compared with the measure of all the time period..

How to avoid this behavior..

Regards,

Satya

Re: How to achieve the below task?

sunny_talwar — Wed, 08 Jun 2016 15:31:44 GMT

I am getting a different average for Feb = 4.4?

Re: How to achieve the below task?

sunny_talwar — Wed, 08 Jun 2016 15:35:51 GMT

Try this script:

Table:

LOAD Month(Date#(Month, 'MMM')) as Month,

Sale,

RowNo() as Key;

LOAD * INLINE [

Month, Sale

Jan, 2

Jan, 9

Jan, 4

Jan, 5

Jan, 6

Jan, 8

Jan, 1

Feb, 2

Feb, 3

Feb, 4

Feb, 5

Feb, 8

Mar, 6

Mar, 8

Mar, 1

Mar, 2

Mar, 3

];

Dimension: Month

Expression:

=Count({<Key = {"=Sale < Avg(TOTAL <Month> Sale)"}>}Sale)

Re: How to achieve the below task?

narayanamsn — Wed, 08 Jun 2016 16:08:33 GMT

Hi Sunny,

Thank you for the rocket speed response. Can you explain the evaluation of the formula...

Alternate

I created a variable for average vAvg and want to pass this instead of writing the avg formula in the if statement..

How to modify your formula to accommodate the variable..

I have another scenario with Fractile function.. Hence thought of understanding your expression properly...with variable

Regards,

Satya

Re: How to achieve the below task?

sunny_talwar — Wed, 08 Jun 2016 16:42:35 GMT

For each row (using RowNo() I have created a unique row), I am checking if Sale is less than Avg of sale for the month (Avg(TOTAL <Month> Sale)). If it is, then include the key, otherwise don't include it.

Does it make sense?

Re: How to achieve the below task?

narayanamsn — Wed, 08 Jun 2016 17:16:53 GMT

absolutely.. It makes sense.. I am trying to get the same result using variable !

Script..

Table:

LOAD Month(Date#(Month, 'MMM')) as Month,

Sale,

RowNo() as Key;

LOAD * INLINE [

Month, Sale

Jan,2

Jan,9

Jan,4

Jan,5

Jan,6

Jan,8

Jan,1

Feb,10

Feb,27

Feb,36

Feb,45

Feb,7

Mar,25

Mar,70

Mar,50

Mar,30

Mar,5

];

Variable vAvg= Avg(Sale)

Below is the image.. I need to use variable in the set analysis to fetch data ..Is it possible..

vAvg is always 20 in this case (irrespective of the month.) but my request is how to get the same values that you plotted in left table using the variable..

Re: How to achieve the below task?

sunny_talwar — Wed, 08 Jun 2016 17:30:34 GMT

Check the updated file with the variable created

Re: How to achieve the below task?

narayanamsn — Wed, 08 Jun 2016 20:13:34 GMT

Fantabulous..

One thing , I am unable to understand is why to update Key = in set analysis..

what does it mean. (we are comparing sales value with Avg sales and what role does Key= play here..

Count({<Key = {"=Sale < $(vAvg)"}>}Sale)

Re: How to achieve the below task?

sunny_talwar — Wed, 08 Jun 2016 20:27:56 GMT

In order to do a unique combination check, we need to have a unique key which can uniquely associate a month sale number to the Avg for the month. In this sample it was as simple as creating a unique key. In another instance you might be able to create another unique combination.

To see this, create a straight table with Key as your dimension and three expressions

1) Sale

2) $(vAvg)

3) =If(Sale < $(vAvg), Key)

You will see that the third expression will show only those keys which needs to be counted.

I hope this will help clarify some of the doubts

Re: How to achieve the below task?

narayanamsn — Thu, 09 Jun 2016 08:32:14 GMT

Hi Sunny,

The solution is not working for the Fractile function. Can I have chat with you...

Re: How to achieve the below task?

narayanamsn — Mon, 13 Jun 2016 09:22:54 GMT

Hi Sunny,

Thank you for the help..

I have an excel with 7 columns and used as source to Percentile QV.

Seller:

LOAD Seller,

[Supply Time],

[Part Name],

Month,

Delivery,

Order,

sub

FROM

(txt, codepage is 1252, embedded labels, delimiter is ',', msq);

Now how to add RowNo() to this script.

Please find attached QV which is showing aggregated numbers even though month was selected as dimension.

Ex:

Filters : Delivery : Now and Tomorrow Seller : D Part Name : CPU

Jan'14 has 6440 which are less than 90th percentile of supply time (5).. But if you clear of selection on Month , it shows 6226 as the 90th percentile for overall data set is 3 and 6226 records have supply chain time less than 3.

How to get the 6440 against Jan even if I do not select month..

QVW is attached for the reference.

Regards,

Satya

Re: How to achieve the below task?

sunny_talwar — Tue, 14 Jun 2016 01:33:13 GMT

I responded to your new post. Please check and revert back to confirm if it works or not