Loading Excel Data into QlikView

[Anonymous]

Hi All,

Any idea on how can I load my data based on the ROMth?  Meaning that the curentbookbalance should not be loaded as a whole, instead it should be grouped by their respectively ROMth.  The end product should be 12 different ROMth which represents the current book balance for that specific month.  The reason for this is to get each month's balance which is calculated by last month's balance - run off amt for the current month. The graph will be shown as a stacked bar chart shown below which would also show the different products in that month.

[Gysbert_Wassenaar]

In qlikview it's common to load the data at the detail level and then create charts and tables in the front end where you aggregate the data. You would use ROMth as the first dimension in a bar chart, Product as the second dimension and sum(CurrentBookbalance) as expression.
I'm not sure that gives you exactly what you want. ROMth value 'Jan' exists in both ROYr 2020 and 2021. Are you sure the CurrentBookbalance of those two records should be grouped together in 'Jan'?

[Anonymous]

Hi, the result came out to be the same as my current graph that I have created earlier. I would have to deduct the runoff amt from the individual months thus I would need them to be loaded separately. For example, instead of 'currentbookbalance', I would need to have 'JanBal' which is just a part of currentbookbalance. Yes, they will be group together as long as they are of the same month, this is because I will have a separate chart for the user to choose the year first.

[Anonymous]

This is how it looks like currently

[Gysbert_Wassenaar]

I'm afraid I don't understand what you're trying to explain. I'm guessing you need something calculated and that involves the currentbookbalance column and then runofamt column. Can you give an example with numbers of how the number you want to calculate is calculated?

[Anonymous]

For example, to calculate Aug balance, (Aug bal = July balance - Aug Runoff) which is  $2,669,342 - $1,180,198 = $1,489,144 (this should be the amount shown in the stacked bar for Aug)