topic Re: HELP in QlikView

HELP

panipat1990 — Wed, 25 Nov 2020 16:16:04 GMT

Dear Team,

I stuck a requirement Kindly help me .

I Have a Sale data.i want to Caluclate some figure ..

Header 1

Header 2

Header 3

Header 4

Header 5

Header 6

TOP 50% volume contributors

Next 30%

Balance 20%

SUMMARY

TOTAL

MARKET TYP

MARKET SIZE

1000

500

300

200

HERE TOAL IS SHOWING TOTAL PERSON WHO SALED THINGS. AND SUM SALES

A:-SHOWING 50% SALE WITH COUNT OF THOSE PERSON WHO SALED 50% THNGS

B:-SHOWING 30% SALE WITH COUNT OF THOSE PERSON WHO ONLY SALED 30 %

C:-SAME SHOWING ONLY 20%

For this i am sharing data

Re: HELP

Digvijay_Singh — Mon, 22 May 2017 01:48:24 GMT

Check this out, my results defer a bit from expected one, please check and revert -

Data: //Load source data

LOAD Dearler,

Sale

FROM

Testing_excel.xlsx

(ooxml, embedded labels, header is 4 lines, table is Sheet1);

Left join(Final) //Calculate Total Sale Sum for further use

Load

Sum(Sale) as TotalSale

Resident Final

;

Left join(Final) //Calculate PercentSale

Load

Sale/TotalSale as PercentSale

Resident Final;

NoConcatenate

Final1: //Calculate sum of percents and sort records in descending order

Load *,

rangesum(PercentSale,Peek(PercentSum)) as PercentSum

Resident Final

Order By Sale desc;

Drop table Data,Final;

Re: HELP

panipat1990 — Mon, 22 May 2017 05:02:04 GMT

Kindly tell me where is final table which using for left join purpose in script level

Re: HELP

panipat1990 — Mon, 22 May 2017 05:45:05 GMT

And Peek(PercentSum) How can i can use..PercentSum.Because PercentSum is finding this formula

rangesum(PercentSale,Peek(PercentSum))

Re: HELP

Digvijay_Singh — Mon, 22 May 2017 05:50:21 GMT

Didn't get you?

Initially PercentSum will be null, thats the reason I used rangesum, so the first value will be equal to PercentSale and it is assigned to PercentSum. From next row onwards, You get the PercentSum from the previous row through Peek function.

Re: HELP

Digvijay_Singh — Mon, 22 May 2017 05:53:14 GMT

For first row, PercentSum -

rangesum(PercentSale,Peek(PercentSum)) means rangesum(0.30,Null) = 0.30

For second row -

rangesum(PercentSale,Peek(PercentSum)) means rangesum(0.44,0.30) = 0.74

Re: HELP

Digvijay_Singh — Mon, 22 May 2017 05:56:01 GMT

My bad, I made last minute change and made mistake -

Here is the updated script -

Data: //Load source data

LOAD Dearler,

Sale

FROM

Testing_excel.xlsx

(ooxml, embedded labels, header is 4 lines, table is Sheet1);

Left join(Data) //Calculate Total Sale Sum for further use

Load

Sum(Sale) as TotalSale

Resident Data

;

Left join(Data) //Calculate PercentSale

Load

Sale/TotalSale as PercentSale

Resident Data;

NoConcatenate

Final1: //Calculate sum of percents and sort records in descending order

Load *,

rangesum(PercentSale,Peek(PercentSum)) as PercentSum

Resident Data

Order By Sale desc;

Drop table Data;

Re: HELP

panipat1990 — Mon, 22 May 2017 05:56:52 GMT

But in script level where is final table. used left join final

Re: HELP

Digvijay_Singh — Mon, 22 May 2017 05:58:28 GMT

Just responded on that with updated script.

Re: HELP

panipat1990 — Mon, 22 May 2017 08:13:39 GMT

Dear Sir,

If i do 50% of 16098 then it come 8049 but in above 7147 is coming.Kindly help me for resolving this.

Re: HELP

panipat1990 — Mon, 22 May 2017 09:40:09 GMT

Still be waiting

Re: HELP

panipat1990 — Mon, 22 May 2017 12:29:21 GMT

Still be waiting

Re: HELP

Digvijay_Singh — Mon, 22 May 2017 13:00:14 GMT

Normally It doesn't work like that. In your data you have 2 top values with 4895 and 2252, both contribute to total 44%.

Now the third top value 1528 if added becomes 53% of total contribution.

Now here I need to know how do you want to move forward, if we include 3rd value in the 50% expression, the total will become more than 50%(its 53%)

The ideal situation of having exact values at 50%, 80% etc generally won't be there in real data.

If you want exact values of 50%, 80%, we can show by direct calculation but how will you distribute the count as they don't exactly falls into the 50%, next 30% and next 20% boundaries. See the data below -

Let me know how do you want, accordingly will make the changes

Re: HELP

Digvijay_Singh — Mon, 22 May 2017 14:04:51 GMT

See the attached where I calculated Sum as you desired but normally not preferred, also in count I included values which are near to 50%, 80%