Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

ugurarslan
New Contributor

Table: Create a formula to see the cash received per day offset against total month invoiced amount

Dear all,

I need to offset cash applied USD per day in May 2018 against the total invoiced amount USD in May 2018, in one table.

Expression used for invoice amount USD in May 2018 which has a result off 70.000 USD.

=sum({<[Report Year Month]={'201805'},[Type]={'INV'}>}[Invoice AMT USD]

Expression used for cash applied for May 2018  which has a result off 55.000 USD.

'=sum({<[Report year month]={'201805'},[Application type]={'CASH'}>}[Cash applied AMT USD]

=day([Report Date]) = used for May 2018 day number

The table which I need to create is to see what the % of cash applied per day for May 2018 is against the total invoice in May 2018.

The final result in QlikSense should be:

Day number% of cash receipt per day
12.9%
21.4%
30.7%
44.3%
53.6%
55.7%
61.1%
70.3%
81.4%
92.9%
101.7%
111.0%
121.1%
130.4%
145.7%
154.3%
161.4%
172.9%
183.6%
190.7%
204.3%
212.9%
221.4%
232.1%
244.3%
253.3%
261.0%
271.1%
280.3%
291.9%
304.6%
314.3%

And the calculation is as following where in the dimension I need to fill in =day([Report Date]), what do I need to use as a measurement in order to get the right  %?

Day numberCash applied Invoiced amount USD % of cash receipt per day
1$2,000.00 / $70,000 = 2.9%
2$1,000.00 / $70,000 = 1.4%
3$500.00 / $70,000 = 0.7%
4$3,000.00 / $70,000 = 4.3%
5$2,500.00 / $70,000 = 3.6%
5$4,000.00 / $70,000 = 5.7%
6$800.00 / $70,000 = 1.1%
7$200.00 / $70,000 = 0.3%
8$1,000.00 / $70,000 = 1.4%
9$2,000.00 / $70,000 = 2.9%
10$1,200.00 / $70,000 = 1.7%
11$700.00 / $70,000 = 1.0%
12$800.00 / $70,000 = 1.1%
13$300.00 / $70,000 = 0.4%
14$4,000.00 / $70,000 = 5.7%
15$3,000.00 / $70,000 = 4.3%
16$1,000.00 / $70,000 = 1.4%
17$2,000.00 / $70,000 = 2.9%
18$2,500.00 / $70,000 = 3.6%
19$500.00 / $70,000 = 0.7%
20$3,000.00 / $70,000 = 4.3%
21$2,000.00 / $70,000 = 2.9%
22$1,000.00 / $70,000 = 1.4%
23$1,500.00 / $70,000 = 2.1%
24$3,000.00 / $70,000 = 4.3%
25$2,300.00 / $70,000 = 3.3%
26$700.00 / $70,000 = 1.0%
27$800.00 / $70,000 = 1.1%
28$200.00 / $70,000 = 0.3%
29$1,300.00 / $70,000 = 1.9%
30$3,200.00 / $70,000 = 4.6%
31$3,000.00 / $70,000 = 4.3%

in New to Qlik Sense Reply

Tags (1)
4 Replies
agigliotti
Honored Contributor II

Re: Table: Create a formula to see the cash received per day offset against total month invoiced amount

maybe this:

sum( {< [Application type]={'CASH'} >} [Cash applied AMT USD] )

/

sum( {< [Type]={'INV'} >} TOTAL <month> [Invoice AMT USD] )

manojkumar_mk
New Contributor II

Re: Table: Create a formula to see the cash received per day offset against total month invoiced amount

Hi,

Please select Straight table and select dimension as a day([Report Date]) .

1. select measure as

sum({<[Application type]={'CASH'}>}[Cash applied AMT USD]


2. select second measure as

sum({<[Type]={'INV'}>}[Invoice AMT USD]


3. select third measure for % of Cash receipt per day

use expression: (Column(1)/column(2))*100


      or else


Num((sum({<[Application type]={'CASH'}>}[Cash applied AMT USD]

/

sum({<[Type]={'INV'}>}[Invoice AMT USD] ) * 100,'#.#%')


or else

(sum({<[Application type]={'CASH'}>}[Cash applied AMT USD]

/

sum({<[Type]={'INV'}>}[Invoice AMT USD] ) * 100



Cheers,

Manu


ugurarslan
New Contributor

Re: Table: Create a formula to see the cash received per day offset against total month invoiced amount

Dear Andrea,

The result is very close to the answer I am looking for. But with using this:

sum( {< [Application type]={'CASH'} >} [Cash applied AMT USD] )

/

sum( {< [Type]={'INV'} >} TOTAL <[Report year month]> [Invoice AMT USD] )


I get all months, I want to see only 201805.


Do you know how I can get that?


Thank a lot!!!


agigliotti
Honored Contributor II

Re: Table: Create a formula to see the cash received per day offset against total month invoiced amount

you can use an external filter on [Report year month] field

OR

sum( {< [Report Year Month]={'201805'}, [Application type]={'CASH'} >} [Cash applied AMT USD] )

/

sum( {< [Report Year Month]={'201805'}, [Type]={'INV'} >} TOTAL <[Report year month]> [Invoice AMT USD] )