Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Dimension Help

I was hoping that someone would be able to advise me on a dimension issue that I have.

I currently have an application that calculates the cost ratio on an annual basis.  All costs that fall within a particular year / earned amount of money collected within that particular year.

The only way I have managed to do this is by pure expressions using set variables for the start and end (and cut) date of each year.

What I would like to do is ditch the pure expression for each year and actually have year as a dimension.  I have tried to do this several times and have never succeeded; can anyone help?

Many thanks

4 Replies
jvitantonio
Luminary Alumni
Luminary Alumni

add a date field to your table and join it to the Caldendar so you can use the Year field as the dimension

Not applicable
Author

Thanks for your suggestion.

I have tried this (Attached) and although it works for the 'Costs' element; I cannot get the income (collected) figure to work.

I have separted this out within the table.

Many thanks

jvitantonio
Luminary Alumni
Luminary Alumni

Ted, sorry but I don't understand what you need to do with the values. Forget about what you've done. Could you please tell me what formula do you need to use using the fields in your tables (costs and transactions)?

If the formula is too complex, simplify it so we can work it better. I want to spot what is that you can't do.

Not applicable
Author

Hi,

Main Formula (Works out ratio)

=((sum((if (Adt > V2011 ,' ' , if(Adt < VYS2011 ,' ' ,total)))))
/
(
sum(collected/ days2 * if (transaction > V2011 ,0 , if(coverto < VYS2011,0 , if(coverto > V2011, if (transaction > VYS2011, V2011 - transaction + 1, V2011 - VYS2011 + 1), if(transaction > VYS2011, coverto - transaction + 1, coverto - VYS2011 + 1))))) 
))

Part 1

sum((if (Adt > Variable1 ,' ' , if(Adt < Variable2 ,' ' ,total))))

This is the straight forward part; it sums the total cost for a given year (Variable1 being Yearend and Variable2 being Yearstart).

This can easily go into a table with ‘Year’ as the expression to work out total cost.

Part 2

(sum(collected/ days2 * if (transaction > V2011 ,0 , if(coverto < VYS2011,0 , if(coverto > V2011, if (transaction > VYS2011, V2011 - transaction + 1, V2011 - VYS2011 + 1), if(transaction > VYS2011, coverto - transaction + 1, coverto - VYS2011 + 1))))))

This works out the total money collected spread over the period of cover (coverfrom and coverto) based upon transaction date.

Example:

Start Date 1/11/10

End Date   31/10/11

Money Paid £12,000

Very roughly you would have £2,000 showing for 2010 and £10,000 showing for 2011.

This needs to be done on a transaction basis.

In the same example if there were £1,000 costs for 2010 and £2,000 for 2011; the ratio for 2010 would be 50% and 20% for 2011.

I hope this makes more sense