Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Discussion Board for collaboration related to QlikView App Development.

- Qlik Community
- :
- All Forums
- :
- QlikView App Dev
- :
- Set Analisis problem

Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

sergio

Contributor III

2009-10-05
04:38 PM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Set Analisis problem

Hi there, i'm a new user of QlikView and i enconunter this tool very useful and powerfull to manage the accounting. Few days ago i started to do a complete balace sheet, but i have a little problem with the set analysis on a pivot table to calculate amounts of money. I will try to explain my problem clearly:

I have periods of time like this:

**Periods (Name of the field: AsiPeriodo)**

2009-10 |

2009-9 |

2009-8 |

2009-7 |

2009-6 |

2009-5 |

2009-4 |

2009-3 |

2009-2 |

2009-1 |

And my balance sheet for 2009-8 is like this:

Title | Group | Account | Balance |

Caja | MONEDA | DOLARES ESTADOUNIDENSES | 1.007.628,40 |

Caja | MONEDA | CAJA MONEDA NACIONAL | 22.037,39 |

Caja | MONEDA | Total | 1.029.665,79 |

Caja | Total | 1.029.665,79 | |

Total | 1.029.665,79 |

My idea is to select any field of the period (only one, e.g.:'2009-8') and obtain the total of that account up to that year-month. In the expression i wrote this:

sum({1 <AsiPeriodo=$(AsiPeriodo)>} Asi1Importe*Asi1DoH)

With this formula i have the total of the account but of all the periods (the sum from 2009-1 to 2009-10), that's not what i want. I need the total but up to the year-month that i chose (the correct will be the sum from 2009-1 to 2009-8). So i need a little help with this.

I will be very grateful with any response and for your time. And excuse my english, is not very good.

Regards.

Sergio

554 Views

1 Solution

Accepted Solutions

sergio

Contributor III

2009-10-06
04:36 PM

Author

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

John Witherspoon wrote:

I may just be confused on what you are saying, but I would caution against managing your accounting in QlikView. QlikView is subject to minor mathematical errors, and your accounts might not balance correctly. I would only use QlikView for account reporting when the numbers don't have to be exact. Follow this link for more information in the errors and one way to work around them if necessary:

http://community.qlik.com/wikis/qlikview-wiki/explanation-of-mathematical-errors-in-qlikview.aspx

On to your actual question. Is AsiPeriodo a QlikView date with format 'YYYY-M'? If so, I would probably handle it like this (untested):

sum({<AsiPeriodo={">=$(=date(yearstart(max(AsiPeriodo)),'YYYY-M')) <=$(=date(max(AsiPeriodo),'YYYY-M'))"}>} Asi1Importe*Asi1DoH)<div></div>

Hi John, thank you for answer. Your formula was very close, it was not the correct but it gave me a lot of ideas, i changed a few things, the correct one for my application was:

sum({1<AsiPeriodo={"<=$(=date(=only(AsiPeriodo)),'YYYY-MM')}>} Asi1Importe*Asi1DoH)

However, you were rigth, i didn't know about the mathematical errors, so, when i finished the balance sheet i had differences between 0.01 and 0.05 cents, i will have to change the source table to avoid the formula in qlikview.

Regards.

170 Views

3 Replies

johnw

Champion III

2009-10-05
08:56 PM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

I may just be confused on what you are saying, but I would caution against managing your accounting in QlikView. QlikView is subject to minor mathematical errors, and your accounts might not balance correctly. I would only use QlikView for account reporting when the numbers don't have to be exact. Follow this link for more information in the errors and one way to work around them if necessary:

http://community.qlik.com/wikis/qlikview-wiki/explanation-of-mathematical-errors-in-qlikview.aspx

On to your actual question. Is AsiPeriodo a QlikView date with format 'YYYY-M'? If so, I would probably handle it like this (untested):

sum({<AsiPeriodo={">=$(=date(yearstart(max(AsiPeriodo)),'YYYY-M')) <=$(=date(max(AsiPeriodo),'YYYY-M'))"}>} Asi1Importe*Asi1DoH)

170 Views

Not applicable

2009-10-05
08:56 PM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Try this: sum({<AsiPeriodo=$(AsiPeriodo)>} Asi1Importe*Asi1DoH)

170 Views

sergio

Contributor III

2009-10-06
04:36 PM

Author

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

John Witherspoon wrote:

I may just be confused on what you are saying, but I would caution against managing your accounting in QlikView. QlikView is subject to minor mathematical errors, and your accounts might not balance correctly. I would only use QlikView for account reporting when the numbers don't have to be exact. Follow this link for more information in the errors and one way to work around them if necessary:

http://community.qlik.com/wikis/qlikview-wiki/explanation-of-mathematical-errors-in-qlikview.aspx

On to your actual question. Is AsiPeriodo a QlikView date with format 'YYYY-M'? If so, I would probably handle it like this (untested):

sum({<AsiPeriodo={">=$(=date(yearstart(max(AsiPeriodo)),'YYYY-M')) <=$(=date(max(AsiPeriodo),'YYYY-M'))"}>} Asi1Importe*Asi1DoH)<div></div>

Hi John, thank you for answer. Your formula was very close, it was not the correct but it gave me a lot of ideas, i changed a few things, the correct one for my application was:

sum({1<AsiPeriodo={"<=$(=date(=only(AsiPeriodo)),'YYYY-MM')}>} Asi1Importe*Asi1DoH)

However, you were rigth, i didn't know about the mathematical errors, so, when i finished the balance sheet i had differences between 0.01 and 0.05 cents, i will have to change the source table to avoid the formula in qlikview.

Regards.

171 Views