How to write a measure for y axis which will take into account x axis value ?

[ok5454564]

Hi,

My question seems really basic but i don't find the solution...

I want to calculate a value like that : SUM(Amount_sales_not_paid) for Date<(Date in X axis)

Sum of Amount_sales_not_paid depend of amount_sales_not_paid for each contract (and for a given contract, this amount can be > 0 and then be equal to 0 if client paid the amount he has to pay).

I am new to Qlik and i cant find how to do that... 

For information, this is what i tried (simple test but what i need is quite more complicated because amount_sales_not_paid is not fixed in the time so i have also to select this value according to the date on x axis... and for each contract)

sum({$<[Date_contract]={“$(=max([Date_contract]))”}>}Amount_sales_not_paid)

[Digvijay_Singh]

Pl share sample data and expected output, that helps to understand the problem quite easily.

[ok5454564]

Hi,

Ok here is an example :

Client	Contract	Date	Amount_sales	Amount_sales_not_paid
C1	Co1	01/01/2023	1000	1000
C1	Co2	01/01/2023	1500	1500
C1	Co2	07/01/2023	1500	1000
C2	Co3	01/01/2023	2000	2000
C2	Co3	07/01/2023	2000	2000
C2	Co3	13/01/2023	2000	0

I want a graphic with date on x-axis (continous). And then on y-axis i want measure depending on date in x-axis :

- for x = 01/01/2023 : y = (1000+1500+2000)/(1000+1500+2000) = 1 (100% unpaid)

same value until 07/01/2023

- for x = 07/01/2023 : y = (1000+1000+2000)/(1000+1500+2000) = 0.89 (89% unpaid)

same value until 13/01/2023

- for x = 13/01/2023 : y = (1000+1000+0)/(1000+1500+2000) = 0.44 (44% unpaid)

[ok5454564]

@Digvijay_Singh is it possible to do that ? 

[Digvijay_Singh]

It might be complex but I think its possible but I would try using script rather than front-end. Didn't try yet through script.

Checking what Gurus think about it, thanks in advance! 

@rwunderlich  @marcus_sommer 

[marcus_sommer]

It might be possible within the UI but it's depending what you exactly mean with a continuous axis and if it's fit to the chart-feature respectively which data-structure it would need and if this would work for each wanted view.

So I'm not sure. Unclear is for instant are your example-records single records or accumulated ones? Do fact-records or dimension-values exists for the intermediate dates? In regard to which selections should the view work?

Like @Digvijay_Singh mentioned it might be necessary respectively sensible to transfer a bigger part of the logic to the script. This could mean to:

• accumulate the values per peek() / previous() within sorted resident-loads
• interpolate the not existing dates
• creating offset-values between the dates (showing them instead of dates)
• creating various flags (examples: last week/month or last n days or ...)
• maybe some aggregations (volume and numbers per date or offset-value)
• a second calendar or some kind of as-of-table to the UI navigation
• ...

  Further you may need changes and/or extensions to your calculation because in your attempt you create with $(= ...) an adhoc-variable which is calculated ones before the chart and the result is applied for all rows. This means you couldn't differ with it on different clients and contracts. This means such max(Date) needs to evaluated within an if-loop and/or within aggr() constructs to define a certain dimensionality for the calculation.

The more logic you could bring in the data-model the easier becomes the development of the UI.