Year to Date Sum of Credits by customer with different contract year per customer

[#1]

Hi,

I am fairly new here, and would love some guidance from the Qlik gurus.

I'm trying to determine how many credits my customers have used year-to-date, but based on a different contract start date for each customer.

For each [CustomerName]

    [ContractYearStartDate] is the date their current contract year started

    [TransactionDate] is the date dimension (I've used a master calendar so there are no gaps)

    [Credits Used] is the number of credits that [CustomerName] used on that [TransactionDate]

    [DaysIntoContractYear] is the number of days this [CustomerName] is into their current contract year on this [TransactionDate]

I tried to do this in my load script and got stumped, so I tried to do this via set analysis... and got stuck there too:

    sum({<[DaysIntoContractYear] ={"<= [DaysIntoContractYear]"}>} [Credits Used])

I know it's possible... but I'm totally stumped. Can anyone lend me a clue?

Cheers,

Jamie

[settu_periasamy]

Hi,

Not sure about your requirement. But in your set expression, We can't give the Filed name directly in the condition.

      sum({<[DaysIntoContractYear] ={"<= [DaysIntoContractYear]"}>} [Credits Used]) - Incorrect

It should be some thing like.

      sum({<[DaysIntoContractYear] ={"<= $(=Max([DaysIntoContractYear]))"}>} [Credits Used])

[jonathandienst]

I am not sure what the problem is.

Do you want to show total credits to year to date? Then use TransactionDate (or related calendar fields like TransactionMonth, ...) as a dimension, and a sum [Credits Used] with set expression like:

     {<TransactionDate = {">=$(=YearStart(Max(TransactionDate ))) <=$(=Date(Max(TransactionDate)))"}>}

Or do you want to show the credits by contract age (DaysIntoContractYear)? The use DaysIntoContractYear (or a calc dimension or derived field such as ContractMonth = DaysIntoContractYear / 30) as a dimension. Sum [Credits Used].

[#4]

I'm actually trying to show the credits year to date on that day for each customer.

What I've been trying to do is to find a way to reference that specific row of data either in the set analysis expression or in the load script. So something like the following (the syntax is totally wrong, but hopefully my intent comes through):

  Sum( <[DaysIntoContractYear]={""<=$(=TheValueForThisRow([DaysIntoContractYear]))}> [Credits Used])

(I would also add logic for CustomerName, but I'm leaving that out for simplicity right now)

Is there a way to do that with Set Analysis?

Alternatively, can you see a way to create a new field in the load script that accomplishes this?

And thank you for your help

[#5]

I'm actually trying to show the credits year to date on that day for each customer... so I'm trying to find a way to reference the current row of data and pass a value from that row into the logic of a set analysis expression...

If set analysis isn't intended to work like this, do you know if there is a different type function that would work?

[settu_periasamy]

What about your dimensions? Do you want to cumulative Credit Used for each DaysIntoContractYear?

Can you post the example?

[#7]

Hi Settu,

You're exactly right that I want to do a cumulative Credits Used for each DaysIntoContractYear for each customer.

I was able to resolve this in the load script in an inelegant but functional way.

- Use Order By CustomerID, TransactionDate

- Make ytdCreditsUsed = to the sum of a series of if statements with Peeks:

        (CreditsUsed

        + IF(DaysIntoConYear>=1, Peek([Credits Used], -1),0)

        + IF(DaysIntoConYear>=2, Peek([Credits Used], -2),0)) AS ytdCreditsUsed

Not at all pretty, but it got the job done. If I were dealing with a really large set of data I'd need to devote more time to an efficient solution, but I have around 1mm rows of data, so it's good enough for now.

Thank to you and Jonathan for the help.

Cheers,

Jamie