Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
bharatkishore
Creator III
Creator 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
petter
Partner - Champion III
Partner - Champion III

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

View solution in original post

9 Replies
petter
Partner - Champion III
Partner - Champion III

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
Creator III
Creator III
Author

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
Creator III
Creator III
Author

stalwar1‌ Can you please help me Sunny Bhai..

niclaz79
Partner - Creator III
Partner - Creator III

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
Creator III
Creator III
Author

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.

petter
Partner - Champion III
Partner - Champion III

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?

petter
Partner - Champion III
Partner - Champion III

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
Creator III
Creator III
Author

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

bharatkishore
Creator III
Creator III
Author

Thank you Peter.. Missed it..