Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
Milaf
Contributor III
Contributor III

Parametric data extraction with month-based column structure

Hello,

There is an issue that I have to solve very quickly and I wanted to consult your knowledge. I have a table where the data of each month is kept in a separate column and in the table I want to create, I should show the actual values such as this month, last month, 3 months ago.

I created the following parameter to get the month I want from the columns:
vActual: ='Act_'& [Month(Num)]
(gives October data: like Act_10.)

For 3 Months before
v3MonthsAgo: =Date(AddMonths(Max(YearMonth),-2),'YYYYYMM')
sum({<“$(v3MonthsAgo)”= $(v3MonthsAgo)>} “$(vActual)")

However, in all cases, it shows the last month's realization. How can I fix this?

 

Labels (3)
10 Replies
Milaf
Contributor III
Contributor III
Author

I hope someone can see the question 🙏

Kushal_Chawda

@Milaf  How your month column name is? Is it Act_202401, Act_202402? If so, 3 months ago means sum of Act_202410+Act_202409+Act_202408?

Milaf
Contributor III
Contributor III
Author

Column Name: ACT_1, ACT_2...ACT_11, ACT_12 (just month num)

Kushal_Chawda

@Milaf  Then how YearMonth field plays a role here? 3MonthsAgo means ACT_10+ACT_9+ACT_8?

Milaf
Contributor III
Contributor III
Author

No, it shows the value 3 months ago. I need this visualization; 

 

Milaf_0-1730462233134.png

 

p_verkooijen
Partner - Specialist II
Partner - Specialist II

Hi @Milaf 

The variable v3MonthsAgo has 5 Y's instead of 4

=Date(AddMonths(Max(YearMonth),-2),'YYYYYMM')  = 0202409

=Date(AddMonths(Max(YearMonth),-2),'YYYYMM')  = 202409

Kushal_Chawda

@Milaf  Assuming your MonthYear field Format is YYYYMM

v3MonthsAgo: =Date(addmonths(date#(Max(YearMonth),'YYYYMM'),-2),'YYYYMM')

sum({<YearMonth= {">=$(v3MonthsAgo)"}>} [$(vActual)])

 

 

 

 

 

 

Milaf
Contributor III
Contributor III
Author

I wrote it wrong here, the format is correct in the parameter.

Milaf
Contributor III
Contributor III
Author

Unfortunately it didn't work. No matter how I change the parameter, it brings the actual value of the current month.

Milaf_0-1731001830353.png

I added the value of all fields for detailed analysis.

Milaf_1-1731002296119.png