urgent help required to find the avg consumption..??

[dhasharadh]

Hi All need urgent help on the below.

we have to calculate the average but my results are at sub key level..

we have to avoid sub key level and calculate the avg on the top of it..

formula given in requirement doc :

= (NUM_OF_FULLY_CONSUMED * NUM_DAYS) / SUM(NUM_DAYS) WHERE NUM_OF_FULLY_CONSUMED != 0

current avg formula : (which is at sub_key level)

if (SUM_OF_FULLY_CONSUMED <> 0 , (NUM_OF_FULLY_CONSUMED * NUM_OF_DAYS) ) as AVG_DAYS_SUM

chart exp : Sum({<PASS_TYPE={LTE}>}AVG_DAYS_SUM) / Sum({<PASS_TYPE={LTE}>}NUM_OF_DAYS)

new formula am looking for : (with avoiding sub_key)

can any please help me how to wirte this set expression by avoiding SUB_KEY and by including above current avg formula and chart ext...bit urgent please

[vishsaggi]

May be this:

= Sum({< PASS_TYPE={LTE} >} if(NUM_OF_FULLY_CONSUMED <>0, NUM_OF_FULLY_CONSUMED)*NUM_OF_DAYS)

/

sum({< PASS_TYPE={LTE} >}NUM_OF_DAYS)

[dhasharadh]

Hi Nag, thanks for the reply,

in your expression where we are avoiding the sub_key column. if i did not avoid the sub key it is giving me wrong average. please help me how to avoid the sub key in my average calculation..

[dhasharadh]

some one like stalwar1‌ or jagan can look into this please.

[sunny_talwar]

Would you be able to share a sample and elaborate on what is not right and what should it be?

[vishsaggi]

Is Sub_Key a field name? When you say avoid, you meant to disregard selections in that sub_key or remove any values from that field?

[dhasharadh]

Hi Sunny,

attaching the sample data for the query. the same query am using it in Qlikview.

all other KPIs like total sales, fully consumed,partially consumed are fine but average part is bit tricky.

　

now i have to calculate the 'avg num of days for full consumption' (means average number of days for a tariff plan to full consume in a month)

　

tariff plan is coming from : TARIFF_NAME , subscriber key coming from SUB_KEY.

　the formula given for us is below :

(number of passes with same days for full consumption * number of days) / total number of days

= (NUM_OF_FULLY_CONSUMED * NUM_DAYS)/ SUM(NUM_DAYS) WHERE NUM_OF_FULLY_CONSUMED != 0

　

expected result is below 30 for each tariff plan for each month. (not sure but i just want to go with correct procedure to calculate it)

　

and as a note please evaluate the formula we are using, i suspect the formula is wrong some time as it is bit different than our normal avg calculations.

please find the attached sample query and sample data to the main thread and please help me in calculating the correct average. Thanks much in advance.