Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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)
Pl share sample data and expected output, that helps to understand the problem quite easily.
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)
@Digvijay_Singh is it possible to do that ?
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!
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:
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.