Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Highlighted
bharatkishore
Contributor III

Formula help

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:

T.PNG

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

1 Solution

Accepted Solutions
MVP
MVP

Re: Formula help

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

2018-08-10 12_41_11-Edit Expression.png

9 Replies
MVP
MVP

Re: Formula help

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))

bharatkishore
Contributor III

Re: Formula help

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

bharatkishore
Contributor III

Re: Formula help

stalwar1‌ Can you please help me Sunny Bhai..

niclaz79
Contributor III

Re: Formula help

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.

bharatkishore
Contributor III

Re: Formula help

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.

MVP
MVP

Re: Formula help

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

2018-08-10 12_36_29-QlikView x64 - Evaluation Copy - [C__Users_pts_Downloads_T.qvw_].png

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

MVP
MVP

Re: Formula help

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

2018-08-10 12_41_11-Edit Expression.png

bharatkishore
Contributor III

Re: Formula help

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

bharatkishore
Contributor III

Re: Formula help

Thank you Peter.. Missed it..

Community Browser