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: 
MalcolmCICWF
Creator III
Creator III

Dimension Dimentia in Pivot Tables

I am working on a Pivot Table report in Qlikview and running into some issues with a Dimension I am trying to create. I keep getting an "Error in Calculation" statement with everything I try. I am having a tough time working through this one.

For each of the 'TRANTYPE' field I want to sum the amounts under the years, but each trantype sums different dates. My pivot table has each amount broken down by Year(TRANDATE) and then Month(TRANDATE)  created in my script. I also have a static date of of the Year/Month I am refreshing this report called 'CURMONTH'. Both date fields behind the scenes are formatted MMDDYYY.

-I am trying to say for my dimension, if the field TRANTYPE is "ActualPymnts" and the TRANDATE is  <=  the CURMONTH, then give me the sum of the AMOUNT field for those. S looking at the numbers below sum all 2003 through August 2013. That way each month I run the report and have a new CURMONTH, it will continue to add the amounts into this SUM dimension

Is anyone Dimension/Pivot Table savvy enough to help me work through this?

Capture.JPG

7 Replies
MayilVahanan

Hi

Try like this

= Aggr(Sum(AMOUNT),TranDate)

Note: instead of bold expression, use your expression.

Hope that helps

Thanks & Regards, Mayil Vahanan R
Please close the thread by marking correct answer & give likes if you like the post.
MalcolmCICWF
Creator III
Creator III
Author

I still am having issues writing the function, I am not the best at forming them correctly. Can someone help me write this function out or steer me towards a good document on forming functions correctly?

I tried  Aggr(Sum(amount),IF(trantype='ActualPymnts' and trandate<=curmonth)) but I know that is not right. I have a hard time understandingfunction layouts in Qlikview.

MalcolmCICWF
Creator III
Creator III
Author

So as I explained I am trying to SUM al of the the AMOUNT if the TRANTYPE is 'ActualPymnts' and the TRANDATE <= CURMONTH.  I cannot seem to form the function correctly.  I will then need to do an OR statement with another set up criteria.

v_iyyappan
Specialist
Specialist

Hi

try like this,

=Aggr(Sum(amount),IF(trantype='ActualPymnts' and Floor(trandate)<=Floor(curmonth)))


Why we use floor means may be dateformat not same for transdate and curmonth.


If its not correct please give the sample application.


Regards,



MalcolmCICWF
Creator III
Creator III
Author

I tried and to no avail. I can not get it to work inthe dimensions...

I cloned the table I had and removed the month/year dimensions and Amount expression and I can get it to work perfect per the example below as shown by "PeriodTD". but the same function still shows as error in formula when added as a calculated dimension... this makes no sense to me.

here is what works in the Expression: SUM(IF(trantype='OrigForecast' and trandate<=curmonth,amount,0))

 

Capture.JPG

v_iyyappan
Specialist
Specialist

Hi,

Try like this

=Sum({<trantype={'OrigForecast'},trandate={"<={$(=curmonth)}"}>}amount)

Please check the trandate and curmonth are same format otherwise change the curmonth dateformate like trandate.

regards,

MalcolmCICWF
Creator III
Creator III
Author

sorry, still a no go. My dates are formatted the same