Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

MalcolmCICWF
Not applicable

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
Not applicable

Re: Dimension Dimentia in Pivot Tables

Hi

Try like this

= Aggr(Sum(AMOUNT),TranDate)

Note: instead of bold expression, use your expression.

Hope that helps

MalcolmCICWF
Not applicable

Re: Dimension Dimentia in Pivot Tables

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
Not applicable

Re: Dimension Dimentia in Pivot Tables

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
Not applicable

Re: Dimension Dimentia in Pivot Tables

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
Not applicable

Re: Dimension Dimentia in Pivot Tables

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
Not applicable

Re: Dimension Dimentia in Pivot Tables

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
Not applicable

Re: Dimension Dimentia in Pivot Tables

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