topic Re: Dimension Dimentia in Pivot Tables in QlikView

Dimension Dimentia in Pivot Tables

MalcolmCICWF — Wed, 11 Sep 2013 22:04:06 GMT

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?

Re: Dimension Dimentia in Pivot Tables

MayilVahanan — Wed, 11 Sep 2013 22:15:44 GMT

Try like this

= Aggr(Sum(AMOUNT),TranDate)

Note: instead of bold expression, use your expression.

Hope that helps

Re: Dimension Dimentia in Pivot Tables

MalcolmCICWF — Thu, 12 Sep 2013 14:58:30 GMT

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.

Re: Dimension Dimentia in Pivot Tables

MalcolmCICWF — Thu, 12 Sep 2013 15:12:08 GMT

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.

Re: Dimension Dimentia in Pivot Tables

v_iyyappan — Thu, 12 Sep 2013 15:41:12 GMT

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,

Re: Dimension Dimentia in Pivot Tables

MalcolmCICWF — Thu, 12 Sep 2013 16:58:40 GMT

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))

Re: Dimension Dimentia in Pivot Tables

v_iyyappan — Thu, 12 Sep 2013 17:15:29 GMT

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,

Re: Dimension Dimentia in Pivot Tables

MalcolmCICWF — Tue, 24 Sep 2013 16:05:36 GMT

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