topic Drill in TOTAL function in QlikView

Drill in TOTAL function

rkspareek1992 — Mon, 20 Mar 2017 10:17:51 GMT

Hi,

I am using TOTAL function in dril down chart.

When I am showing data in front then its totaling with respect to fields, but when I am drilling chart then its showing same total.

At the result of drill I wants separate values, but when I am using TOTAL function its only showing total values.

kindly Suggest

Below is the example:

Here its calculating A/total (B)

Expression:

sum({<Year={'$(vMaxYear)'},MATKL={'CM*'},[Work Center Sub Group]={'Rotary Furnace'},[Segment Final]={'Lead'}>}Quantity_MSEG_FlagConsumption2)/

Sum(total<[Plant Name]>{<Year={'$(vMaxYear)'},BWART1={'101_102'},[Work Center Sub Group]={'Rotary Furnace'},[Segment Final]={'Lead'},[Material Name]>}Quantity_MSEG)

Material Name	A/B
	31.17
Used Oil	31.17

But when I am using used oil then its also showing same value (31.17), but I wants to select respected values.

Re: Drill in TOTAL function

kkkumar82 — Mon, 20 Mar 2017 10:51:21 GMT

By your output , there is only one value when oil is there I think that is why it is showing same value, if not give a sample example if possible

Re: Drill in TOTAL function

sunny_talwar — Mon, 20 Mar 2017 10:53:36 GMT

Are you using a Drill Down group here?

May be try like this

Sum({<Year={'$(vMaxYear)'},MATKL={'CM*'},[Work Center Sub Group]={'Rotary Furnace'},[Segment Final]={'Lead'}>}Quantity_MSEG_FlagConsumption2)/

Sum(TOTAL <$(='[' & GetCurrentField([DrillDownGroup]) & ']')> {<Year={'$(vMaxYear)'},BWART1={'101_102'},[Work Center Sub Group]={'Rotary Furnace'},[Segment Final]={'Lead'},[Material Name]>}Quantity_MSEG)

Re: Drill in TOTAL function

rkspareek1992 — Mon, 20 Mar 2017 11:01:56 GMT

Hi Sunny,

Your formula is not working. I think I have to use this type of function, but it is not right.

Kindly check..

Re: Drill in TOTAL function

sunny_talwar — Mon, 20 Mar 2017 11:04:05 GMT

What is the name of your DrillDownGroup?

Re: Drill in TOTAL function

rkspareek1992 — Mon, 20 Mar 2017 11:06:15 GMT

Now I am only using one filed which name is PLANT.

Please write correct syntax.

Re: Drill in TOTAL function

kkkumar82 — Mon, 20 Mar 2017 11:08:20 GMT

Helping sunny, rakesh in the chart you might be using a drilldown group as dimension, what is the name of that, that what sunny is asking

Re: Drill in TOTAL function

sunny_talwar — Mon, 20 Mar 2017 11:08:49 GMT

But you mentioned Drill Down chart? What is a drill down chart

Please write correct syntax.

I cannot write the correct syntax unless I know what you are trying to do. I suggest you provide more details or a sample so that we can help you out.

Best,

Sunny

Re: Drill in TOTAL function

sarvesh — Mon, 20 Mar 2017 11:09:00 GMT

=count(total <DrilldownDim1, DrilldownDim2, DrilldownDim3,,,DrilldownDimn> CREST_SERIAL_NO)

Re: Drill in TOTAL function

rkspareek1992 — Mon, 20 Mar 2017 11:21:59 GMT

Hi Sunny,

This is first seen:

As per my expression, A/B= Consumable/Production Qty = 797042 / 25570=31.171

But when I am selecting Used Oil in chart, then its showing same 31.171 value.

It has to show 6579 / 25570=.25

Re: Drill in TOTAL function

sunny_talwar — Mon, 20 Mar 2017 11:34:38 GMT

Are you doing Consumable/Production or Production/Consumable? You mentioned 6579/25570 after selection, but 6579 is production? Why is it in the numerator? Also, this seems like a Qlik Sense question.... can you move the discussion to Qlik Sense so that right people can take a look at this?

Re: Drill in TOTAL function

rkspareek1992 — Mon, 20 Mar 2017 11:43:22 GMT

Sorry,

this is consumable/Production...

This would be 25570/6579.

Re: Drill in TOTAL function

sunny_talwar — Mon, 20 Mar 2017 11:47:15 GMT

Try removing [Material Name] from your denominator expression

Sum({<Year={'$(vMaxYear)'},MATKL={'CM*'},[Work Center Sub Group]={'Rotary Furnace'},[Segment Final]={'Lead'}>}Quantity_MSEG_FlagConsumption2)/

Sum(total<[Plant Name]>{<Year={'$(vMaxYear)'},BWART1={'101_102'},[Work Center Sub Group]={'Rotary Furnace'},[Segment Final]={'Lead'},[Material Name]>}Quantity_MSEG)

Re: Drill in TOTAL function

rkspareek1992 — Mon, 20 Mar 2017 11:54:09 GMT

I cannot remove material name, because data is as like that:

Material Name	Con	Prod	Con/Prod
	797062.00	25570.42	31.17
1	0.00	25570.42	0.00
2	0.00	25570.42	0.00
3	0.00	25570.42	0.00
4	0.00	25570.42	0.00
5	0.00	25570.42	0.00
Used Oil	797062.00	25570.42	31.17

There is no production with respect to Used Oil.

Re: Drill in TOTAL function

sunny_talwar — Mon, 20 Mar 2017 11:55:20 GMT

Where does 6579 comes from?

Re: Drill in TOTAL function

rkspareek1992 — Mon, 20 Mar 2017 11:56:51 GMT

This is data format (in qlikview) not exact values..

Re: Drill in TOTAL function

sunny_talwar — Mon, 20 Mar 2017 12:05:48 GMT

I think somebody else might have a better understanding of your issue. I don't think I am able to understand the issue well enough to help.

Best,

Sunny