Repeat monthly values at day level

[poklegoguy]

Hi, please help me with my problem. I have a Target field in my dataset and it is separated monthly as screenshot below:

here's the thing, I need to create a table where date (every day of a month) is one of my dimension and I will need the Target as one of my measures. I need the Target value to be repeated at every date row in the month as screenshot below:

I have tried expression below:
SUM({$<Month={"$(=Month(Day))"}>} Target

Unfortunately, it doesn't work and I can't find any solution online. I'll appreciate any help! Thanks!

[MatheusC]

Use the Total Qualifier

https://community.qlik.com/t5/Design/What-does-the-TOTAL-qualifier-do/ba-p/1472990


- Regards, Matheus

[poklegoguy]

Hi @MatheusC, thanks for the reply. The 'Total' qualifier indeed worked but only for dates in a month, once the user selected more than a month, it adds up all the the Target values of all selected months and display it at every date row, do you have any idea how can I tweak the expression to get my desired result?

[M_B]

Maybe try to aggregate per month? Not sure if it works but try

Aggr(SUM(total {$<Month={"$(=Month(Day))"}>} Target), Month)

[Qrishna]

method1: if Target for Jan is what you need to fill for other months;' targets.

method2: other methods as per you other needs. let me know

raw:
load * inline [
Year,Month,Target,Sales
2023,Jan,1000,20
2023,Feb,2000,60
2023,Mar,3000,80];

NoConcatenate
Tgt:
Load *,
Date(MonthStart(MakeDate(Year, Match(Month, 'Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun', 'Jul', 'Aug', 'Sep', 'Oct', 'Nov', 'Dec'))), 'MM/DD/YYYY') as MonthStartDate,
if(isnull(previous(Target)), Target,
if(not isnull(previous(Target)), peek(Filled_Target), null())) as Filled_Target // can use any : not isnull(previous(Target)) or peek(Filled_Target) <> ''
resident raw;
drop table raw;

[Kushal_Chawda]

@poklegoguy  tyr below expression

=sum(aggr(sum(Target),Date,Month))

[MatheusC]

I noticed that you already have a Month field, you already have a Year field too, or MonthName?
Basically from what I understand this Day field would be your date field. And you want to replicate the total target according to your month selections.

So my suggestion from what I tried to understand. I would create this Year field:

Year(Day) As FieldYear

And your expression would be like:

sum({<FieldYear={"$(=year(max(Day)))"},FieldMonth={"$(=month(max(Day)))"}>} total [target])

There are already several suggestions, I hope one of these is what you are looking for,
Regards.

[poklegoguy]

Hi @Qrishna, thanks for the reply. Unfortunately my main intention is not filling the other months' target with January's target for example as every month has their own Target. My intention is to fill every date in every month with their own corresponding Target. Thus, there will be a 'Target' column and for dates 1/1/2023 - 31/1/2023 will all have 1000 as their Target and 1/2/2023 - 28/2/2023 will have 2000 as their Target etc. 

[poklegoguy]

Hi @Kushal_Chawda, thanks for the reply. I tried the expression but the problem is that only the last date of the month will have the 'Target' value filled and all the other dates showed 0. Any way I can make the Target value show in every single date row in the month, for example, 1/1/2023 - 31/1/2023 will show 1000 in every row in the 'Target' column and 1/2/2023 - 28/2/2023 date rows will show 2000 as the value.

[poklegoguy]

Hi @MatheusC, thanks for the reply again. I have tried all the suggestion but unfortunately none of them worked so far. Yes, I indeed have a Master Calendar table and month selection isn't necessary what I need. What I desired is that, for example, I need a table that will show every day in a year so 1/1/2023 - 31/12/2023, and I need a Target column which every date should be filled with their corresponding Target of the month, thus, 1/1/2023 - 31/1/2023 will show 1000 as Target value and 1/2/2023-28/2/2023 should show 2000 as Target value etc.