Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
Hi
Try like this
= Aggr(Sum(AMOUNT),TranDate)
Note: instead of bold expression, use your expression.
Hope that helps
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.
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.
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,
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))
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,
sorry, still a no go. My dates are formatted the same