topic Re: Formula help in QlikView

Formula help

bharatkishore — Fri, 10 Aug 2018 09:11:43 GMT

Hi All,

I have period , flag and market as fields in a list box and I am trying to calculate average for YTD .

The problem is when i select period and flag the average value is showing for that month. instead of showing for selected months and dividing by the selected month.

For ex: I have month selected as march. the values for jan and feb and mar are 10,20,30. I need to get output as (10+20+30)/3 = 20

But i here it is happening 30/3 =10.

This is happening only if i select period and if i remove period and select any other combination i am getting value correctly.

Below image for your reference:

Here the value is showing is 12 instead of 38. Can you please help me where i am doing wrong.

Formula i have used is

=If(SubStringCount('|' & Concat(distinct Dimension2, '|') & '|', '|Flag|'),Num(SUM({<Period_NR={'>=$(=MonthStart(vCurrYear_SelectedMnth1, 1-NUM(Month(vCurrYear_SelectedMnth1))))<=$(=vCurrYear_SelectedMnth1)'}

>}IB),'#,##0.0%')/Month(Period_NR))

Please let me know if you need anything.

Thanks,

Bharat

Re: Formula help

petter — Fri, 10 Aug 2018 09:36:08 GMT

If you are using a recent version of QlikView like QlikView November 2017 or newer you should use the correct quotation marks for a search which is double quotes - not single quotes as you have used. In older versions QlikView didn't care whether you had single or double quotation marks for search actually....

=If(SubStringCount('|' & Concat(distinct Dimension2, '|') & '|', '|Flag|'),Num(SUM({<Period_NR={">=$(=MonthStart(vCurrYear_SelectedMnth1, 1-NUM(Month(vCurrYear_SelectedMnth1))))<=$(=vCurrYear_SelectedMnth1)"}

>}IB),'#,##0.0%')/Month(Period_NR))

Re: Formula help

bharatkishore — Fri, 10 Aug 2018 09:44:32 GMT

Hi Peter,

Thank you for your reply.

But still i am getting the same value as 12 instead of 38. It is not summing up. Do i need to use rangesum or anything or i need to do.

Please let me know

Re: Formula help

bharatkishore — Fri, 10 Aug 2018 10:04:37 GMT

stalwar1‌ Can you please help me Sunny Bhai..

Re: Formula help

niclaz79 — Fri, 10 Aug 2018 10:19:52 GMT

Hi,

Check your variables that they are working correctly when selecting the Period_NR by placing them in text boxes and making the selection.

Other than that I'm not seeing any issues, the only thing would be if there are other selections besides the periods that influence the data set and disqualifies the months before March.

Re: Formula help

bharatkishore — Fri, 10 Aug 2018 10:29:56 GMT

I am attaching the file for reference. Please let me know if you need anything more.

When i take expression total 38 is coming correct. But in the chart it is showing 12 only for that month instead of 38.

Kindly check.

Re: Formula help

petter — Fri, 10 Aug 2018 10:38:08 GMT

When I put in the expression I suggested to you in my first response I get the correct result which is 38:

So it seems like you got your answer then doesn't it?

Re: Formula help

petter — Fri, 10 Aug 2018 10:42:52 GMT

If you need to use the expression in a chart you will have to use the TOTAL qualifier in the Sum() like this:

Re: Formula help

bharatkishore — Fri, 10 Aug 2018 10:43:38 GMT

Yes. But in chart it is showing as 12. That is why i don't know where it is getting wrong.

Re: Formula help

bharatkishore — Fri, 10 Aug 2018 10:45:17 GMT

Thank you Peter.. Missed it..