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.