Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Leverage your QlikView investment to modernize BI – see how! Join Group
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator II
Creator II

Summary Data Issue

Hi All,

I have an requirement to show Current Month Plan Unit and Current Year Plan Units.

If user select Year = 2016 and Month = 5

then CMPlan Unit will show = Plan Units for Month 5 and CYPlan Unit should show plan units for 2016.

If I put Month, then it is showing Month Level data correct. But I need to show data in Item Level. Item level data is not showing correct data.

I am attaching qvw file. Please check the table in Sheet2 and the issue is in CYPlanUnits.

for 2016 and Month =5 it should come as 11246, but it is coming as 8482.

we are using distinct, but that is required.

Please help.

Thanks,

Sarif

6 Replies
Highlighted
Champion III
Champion III

Re: Summary Data Issue

Why you have to get 11246? Your Units Planned for Year 2016 has below possible data ? Do we have to consider something else? For year 2016 i have below units planned which sum up to - 8482.

844

890

907

922

937

952

968

984

1078

Highlighted

Re: Summary Data Issue

Hi Mohammad, having to sum 'distinct' values can lead to this kind of issues, where two same values that are equal but have to be summed, you can get a working expression using:

=sum({<CstFiscalPeriod = ,CstFiscalYear = {'$(=$(vMaxYear))'}>}

aggr(sum({<CstFiscalPeriod = ,CstFiscalYear = {'$(=$(vMaxYear))'}>} DISTINCT UnitsPlanned),ItemItemNo,ItemDesc,CstFiscalYear, CstFiscalPeriod))

This way same values in different months can be summed.

If you can avoid the duplicated rows and just do a sum (without 'distinct') it will be better.

Highlighted
Champion III
Champion III

Re: Summary Data Issue

Thanks Ruben, I got what you mean.

Highlighted
Creator II
Creator II

Re: Summary Data Issue

Hello Ruben sir..

seems this is working.

It would be really helpful if you can explain this expression.

I just copied blindly to meet the delivery deadline. 

But I want to make this my own next time. So need your guidance.

I will check thoroughly for other Item no also and will let you know.

Thanks a lot sir..for your time and help..

Thanks,

Sarif

Highlighted

Re: Summary Data Issue

Hi Mohammad, I added the period field to the aggr, so it sums the distinct values by each year and month, this is to sum equal values in different months.

Having this field in aggr will cause that the expression will be affected by selections in the Period field, to avoid this I also need to add the same set analysis to the external sum, so it does the operation for the max year and ignores selections in month.

Creator II
Creator II

Re: Summary Data Issue

Ruben Sir,

I got the first part. But the external Set analysis is not clear to me.

Could you please explain it.

Thanks a lot for your time.

Thanks,

Sarif