topic Re: Daily figure to apply on Monthly database in QlikView

Daily figure to apply on Monthly database

guillaume_gorli — Wed, 13 Sep 2017 17:43:21 GMT

Hi all,

I need some help regarding below issue :

i have two database :

First one is a sale database per date and part number.

Fields are :

Date

Part Number

Sales

i have a second database showing a sales rebate that has to be applied to the dedicated month : for instance a rebate of 2 % has to be applied on all January sales. The tricky point here is that the key between the two tables is a day field.

Even if 2 % is linked to 31/01/2017, it has to apply to all items sold in January.

The issue i am facing is that when i build a table showing the sales (including the rebate) per part number, i only got the items sold the same day as linked with the rebate, ie 31/01/2017 for January month.

The output i get is :

Although what i shall get is :

Attached are .qvw and excel database for the ones you could help.

Thanks

Re: Daily figure to apply on Monthly database

sunny_talwar — Wed, 13 Sep 2017 17:59:03 GMT

Check the attached

Replace

Sum([Rebate rate])

with

Sum(TOTAL [Rebate rate])

Re: Daily figure to apply on Monthly database

guillaume_gorli — Wed, 13 Sep 2017 18:02:49 GMT

Just perfect

Thanks Sunny

Re: Daily figure to apply on Monthly database

guillaume_gorli — Thu, 14 Sep 2017 10:05:53 GMT

Sunny,

if you're still there, how would you write in a text object the formulas showing YTD total rebate ?

For instance,

January rebates are 1,18

February rebates are 2.7

March rebate are 0.21

So the text object should show :

3,88 if February is selected or 4.09 is March is selected.

I was thinking of below formulas but the output is not at all what i should get

RangeSum(Above(Sum({$<Date = {"$(='>=' & Date(YearStart(Max(Date)), 'DD/MM/YYYY') & '<=' & Date(Max(Date), 'DD/MM/YYYY'))"}, Mois, [Année]>}Sales), 0, RowNo()))

RangeSum(Above(Sum(TOTAL{$<Date = {"$(='>=' & Date(YearStart(Max(Date)), 'DD/MM/YYYY') & '<=' & Date(Max(Date), 'DD/MM/YYYY'))"}, Mois, [Année]>}Sales), 0, RowNo()))

Re: Daily figure to apply on Monthly database

sunny_talwar — Thu, 14 Sep 2017 11:13:43 GMT

Try this

=Sum({$<Date = {"$(='>=' & Date(YearStart(Max(Date)), 'DD/MM/YYYY') & '<=' & Date(Max(Date), 'DD/MM/YYYY'))"}, Mois, [Année]>}Aggr(

Sum({$<Date = {"$(='>=' & Date(YearStart(Max(Date)), 'DD/MM/YYYY') & '<=' & Date(Max(Date), 'DD/MM/YYYY'))"}, Mois, [Année]>}Sales) *

Sum({$<Date = {"$(='>=' & Date(YearStart(Max(Date)), 'DD/MM/YYYY') & '<=' & Date(Max(Date), 'DD/MM/YYYY'))"}, Mois, [Année]>}TOTAL <Année_Mois> [Rebate rate]), [Part Number], Année_Mois))

Re: Daily figure to apply on Monthly database

guillaume_gorli — Thu, 14 Sep 2017 11:15:27 GMT

Thanks a lot

Guillaume